update problem in triggers

Started by Jamie Deppelerover 20 years ago2 messagesgeneral
Jump to latest
#1Jamie Deppeler
jamie@doitonce.net.au

I am having with this simple trigger i wrote, worked when i created it
but now i get this error "Stack depth Limit Exceeded"

If someone could tell me what i am doing wrong i would be greatfull

Trigger

CREATE TRIGGER "updateKeys" AFTER UPDATE
ON "projects"."resource" FOR EACH ROW
EXECUTE PROCEDURE "projects"."setParentKeysResourceUpdate"();

Function

CREATE OR REPLACE FUNCTION "projects"."setParentKeysResourceUpdate" ()
RETURNS trigger AS
$body$
declare
projectcursor refCursor;
prop RECORD;
begin
if new.fkproject is null then
open projectcursor FOR SELECT * from projects.component
WHERE projects.component."primary" = new.fkcomponent;
FETCH projectcursor into prop;
update projects.resource set fkproject = prop.fkproject,
fkproposition = prop.fkproposition, fkjob = prop.fkjob where
projects.resource."primary" = new."primary";
close projectcursor;
end if;
return null;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jamie Deppeler (#1)
Re: update problem in triggers

Jamie Deppeler <jamie@doitonce.net.au> writes:

I am having with this simple trigger i wrote, worked when i created it
but now i get this error "Stack depth Limit Exceeded"

You've written an infinite recursion: the trigger does another UPDATE on
the same table that it is an UPDATE trigger for. The approach seems
seriously foolish in the first place --- you should be altering the NEW
record in a BEFORE trigger, not trying to change the record over again
after it's already written out.

regards, tom lane