public inbox for [email protected]  

help / color / mirror / Atom feed

From: Nicolas Mitchell <[email protected]>
To: David G. Johnston <[email protected]>
Cc: hubert depesz lubaczewski <[email protected]>
Cc: [email protected]
Subject: Re: Trigger function
Date: Tue, 27 Jul 2021 23:04:03 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwazmQBDYdt+zcw2WV+3bONRjbe7qoHiMnREYj+VRXEXjA@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<CAKFQuwazmQBDYdt+zcw2WV+3bONRjbe7qoHiMnREYj+VRXEXjA@mail.gmail.com>

On 27 Jul 2021, at 23:13, David G. Johnston wrote:

> On Tuesday, July 27, 2021, Nicolas Mitchell <[email protected]> 
> wrote:
>
>>
>> But when I have tried this with the following trigger/function
>> (BEFORE/AFTER), PG goes into a loop. The two associated sequences 
>> (object,
>> host) are incremented until I break the execution but no insert 
>> happens in
>> either table. My code is causing an endless loop. I’m too green to
>> understand why! I’d be grateful for any hints to help me on my way.
>>
>> CREATE OR REPLACE FUNCTION public.func__host__bi()
>> RETURNS trigger AS
>> $$
>> begin
>>
>> INSERT INTO host (name, domain, object)
>> VALUES (NEW.name, NEW.domain, (SELECT * FROM object_id));
>> RETURN NEW;
>> end
>> $$
>> LANGUAGE 'plpgsql'
>>
>> CREATE TRIGGER trig__host_bi
>>   BEFORE INSERT <————————————> or AFTER 
>> INSERT
>>   ON public."host"
>>   FOR EACH ROW
>>   EXECUTE PROCEDURE public.func__host__bi();
>>
>
> You are getting an infinite cycle because while in the middle of 
> inserting
> a row into host, which provokes the trigger, you go and execute 
> another
> insert command for host, provoking the same trigger, performing yet 
> another
> insert, provoking the same trigger, etc…

I see, I think.

INSERT going to happen > BEFORE INSERT trigger firing function with > 
INSERT INTO host > INSERT going to happen > BEFORE INSERT trigger firing 
function with > etc…

>
> When you write a trigger for a table you should be executing commands
> against the same table.

I believe this implies that the code creating a new object (INSERT INTO 
public.object…) row should reside elsewhere.

>
> You change the data in the ongoing insert by returning a different row 
> from
> the trigger function (i.e., modify your “return new;” line - or 
> modify NEW
> itself?).

I’m absorbing this sentence…

Many thanks for your assistance.

Nic

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Trigger function
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox