Re: Trigger function

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

> On Tuesday, July 27, 2021, Nicolas Mitchell <mitchelln@posteo.net> 
> 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