I am trying to make a trigger where after I insert a painting, then I want to insert it to either the In_Gallery
table or the On_Loan
table but not both. When I tried to make a trigger function, I keep getting the error:
ERROR: stack depth limit exceeded HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
I am not sure what's wrong with this:
CREATE OR REPLACE FUNCTION checkOnLoan()
RETURNS trigger AS
$$
DECLARE
countGal numeric;
BEGIN
SELECT COUNT(*) INTO countGal FROM IN_GALLERY WHERE P_id = new.P_id;
IF countGal = 0 THEN
INSERT INTO ON_LOAN VALUES (new.Certid, new.P_id, new.Insurer);
ELSE
RAISE EXCEPTION 'ALREADY IN GALLERY';
END IF;
RETURN new;
END;
$$
LANGUAGE 'plpgsql';
CREATE TRIGGER OnLoan
AFTER INSERT ON ON_LOAN
FOR EACH ROW
EXECUTE PROCEDURE checkOnLoan();
You INSERT
again in an AFTER INSERT
trigger, causing the trigger to be fired again for this second INSERT
which again INSERT
s and fires the trigger anew and so on and so on. At some point the stack is exhausted from all that function calls and you get the error.
Remove the INSERT
from the trigger functions and just RETURN new
. Returning new
will cause the original INSERT
to be completed. There's no need for a manual INSERT
in the trigger function for AFTER INSERT
triggers.
Like:
CREATE OR REPLACE FUNCTION checkOnLoan()
RETURNS trigger AS
$$
DECLARE
countGal numeric;
BEGIN
SELECT COUNT(*) INTO countGal FROM IN_GALLERY WHERE P_id = new.P_id;
IF countGal = 0 THEN
RETURN new;
ELSE
RAISE EXCEPTION 'ALREADY IN GALLERY';
END IF;
END;
$$
LANGUAGE plpgsql;
And analog for the other trigger function.