I run PostgreSQL-9.2.4
Is it possible to call 2 functions from a trigger?
Let's say I have two functions for two different tables to be executed if the following trigger fires:
Trigger:
CREATE TRIGGER start ON system_status FOR EACH ROW
WHEN ((new.event = start_task))
EXECUTE PROCEDURE ...()
Function 1: (When task starts, remove any previously assigned next task for this system)
CREATE FUNCTION void_next_task() RETURNS trigger AS $$
BEGIN
DELETE FROM tasks_status ts
WHERE ts.system = NEW.system
AND ts.event = 'next_task';
RETURN NEW;
END;
$$
LANGUAGE plpgsql
Function 2:
(If the inserted combination of task and system is already present in the table, then mark any earlier record with this combination as deleted)
CREATE FUNCTION void_dup_task() RETURNS trigger AS $$
BEGIN
UPDATE system_status ss
SET deleted = 'TRUE'
WHERE ss.system = NEW.system
AND ss.task = NEW.task
AND ss.deleted IS FALSE;
RETURN NEW;
END;
$$
LANGUAGE plpgsql
So I ended up with the following ways to resolve it:
- To have a trigger which calls two functions;
- To have a function which performs update on one table and delete on another one;
- To have two exactly same triggers and two different functions;
Before I will go ahead and implement solution 3 could you advice me if solution 1 or 2 are possible at all?