Re: Trigger function

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

> On Tuesday, July 27, 2021, Nicolas Mitchell <mitchelln(at)posteo(dot)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