Reputation: 21
I have a problem with recursion in a trigger.
I have the table:
CREATE TABLE employees(
task int4 NOT NULL,
first_name varchar(40) NOT NULL,
last_name varchar(40) NOT NULL,
PRIMARY KEY (task, cli_id_number)
);
When I insert the values:
(123, name, last)
i want to automatically insert these values as well:
(321, name, last)
I do this in the following way, but apparently the trigger is recursive and after the first recursion it tries to insert the previously inserted value.
CREATE OR REPLACE FUNCTION insert_task() RETURNS trigger AS $BODY$
BEGIN
IF new.task = '123' AND (
SELECT cant FROM (
SELECT name, task, count(*) AS cant
FROM client_task
WHERE name = NEW.name AND task = NEW.task
GOUP BY 1,2
HAVING count(*) <= 1
) t) = 1 THEN
INSERT INTO client_task(task, name, last_name)
VALUES('321', NEW.task, NEW.name, NEW.last_name);
RETURN NEW;
ELSE
IF NEW.task = '321' AND (
SELECT cant FROM (
SELECT name, task, count(*) AS cant
FROM client_task
WHERE name = NEW.name AND task = NEW.task
GROUP BY 1, 2
HAVING count(*) <=1
) t) = 1 THEN
INSERT INTO client_task(task, name, last_name)
VALUES('123', NEW.task, NEW.name, NEW.last_name);
RETURN NEW;
END IF;
END IF;
RETURN NULL;
END; $BODY$ LANGUAGE 'plpgsql';
Any help appreciated.
Upvotes: 2
Views: 901
Reputation: 121534
Use the function pg_trigger_depth()
. According to the documentation it returns:
current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger)
CREATE TRIGGER insert_task
AFTER INSERT ON employees
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE insert_task()
Upvotes: 3