PostgreSQL trigger function update error

Question

i cant seem to logically place this error being produced unlike most gives little to no information as to why (usually seems theirs very good pre-processors or you can dig into the logic, yet here i get no error. also i have another function working well, that adds those keys, so its not that.. other then the transaction seems to fail and i get in the terminal when i enter

 update guest_list set coatcheck = true where ticket_number = 3;

"PL/pgSQL function coatcheck_gen() line 8 at SQL statement SQL statement "update guest_list set coatcheck_num = coat_num where ticket_number = old.ticket_number" PL/pgSQL function coatcheck_gen() line 8 at SQL statement SQL statement "update guest_list set coatcheck_num = coat_num where ticket_number = old.ticket_number"

this goes on for pages, then ends. i've tried uses new. old. or just numbers to see. and nothing. same error. all of the tables are fine. all updates work when just done on command. it appears in examples elsewhere seemly correct...

the function is

 create or replace function coatcheck_gen() returns trigger as $gencoatcheck$
declare
coat_num bigint; 
begin 
IF (TG_OP = 'UPDATE') then
if ( new.coatcheck = true ) then
coat_num := (old.frkey_id_event + old.frkey_id_guest);
update guest_list set coatcheck_num = coat_num where ticket_number = old.ticket_number;
return new; 
END IF;
return new; 
end if; 
return new; 
end;
$gencoatcheck$ LANGUAGE plpgsql;

trigger

create trigger trg_coatchek_gen after update on guest_list for each row when (new.coatcheck = true) execute Procedure coatcheck_gen();

You made a trigger after update for each row and inside trigger you update the same table. Is it your real intention? If it was your idea than what's went wrong? You have an exception in your transaction? You have an exception for each row or you have no exception and it looks wrong for you? — Alex Yu
– Alex Yu, Commented Nov 1, 2017 at 0:41
yup. so, when coat-check bool is modified to true, it will create a coat-check number. and adds it to the table. this only happens after a person is attending. and on the guest list. (functions to create ticket nums, on contributions of type event work all good. i have an exception that insures a person must be "attending" before a coat-check is added. so, trigger occurs when and only when "coatcheck" is chnaged. on those rows. once that occurs the trigger should go to the function and create a coatcheck_number.. — Zepalz
– Zepalz, Commented Nov 1, 2017 at 1:40

JGH · Accepted Answer · 2017-11-01 01:38:53Z

2

You are making an infinite loop by updating the table inside the trigger. You call it first and set the coatcheck = true, then the trigger update the table again but since coatcheck = true it will be again processed by the trigger (and this loop will never end).

You sould replace the entire line

update guest_list set coatcheck_num = coat_num where ticket_number = old.ticket_number;

by

new.coatcheck_num = coat_num;

and make the trigger before update

edited Nov 1, 2017 at 1:38

answered Nov 1, 2017 at 1:16

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zepalz Over a year ago

umm oh! i see what you mean about the loop. though proposed changes. "ERROR: tuple to be updated was already modified by an operation triggered by the current command HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.". umm , "new.coatcheck_num = coat_num" ERROR: column "new" of relation "guest_list" does not exist LINE 1: update guest_list set new.coatcheck_num = coat_num where ti...

JGH Over a year ago

@Zepalz No no, replace the entire line, not just part of it

Zepalz Over a year ago

oh oh. i get it. yes that all makes a looot more sense. and yes that works thankyou.

Collectives™ on Stack Overflow

PostgreSQL trigger function update error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related