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
