Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 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 inserted combination of task and system already presented in the table => mark any earlier records 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 following ways to resolve it:

  1. To have a trigger which calls two functions;
  2. To have a function which performs update on one table and delete on another one;
  3. 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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

A trigger can only ever call one tigger function, so no to item 1.

The preferable form is item 2. IMO. You can put as many SQL statements into a single plpgsql function as you want.

Item 3. is possible, too. Well, not exactly the same trigger, the name would have to be different. Triggers on the same event fire in alphabetical order, btw. But I see no gain in two separate functions. Just more code and overhead and two function invocations, which is more expensive.

2. is the undisputed victor.

share|improve this answer

Just for sake of completeness, there is also number 4 - make ordinary, not trigger functions, and call them both (or just one, depending on some conditions) from the trigger function. This has several disadvantages: you cannot use NEW and OLD in ordinary functions and you have to pass the data to your functions, which means even more overhead than in case 3. In your case, there would be no significant gain for it. The only gain I can imagine is code readability for very complex trigger functions.

I did this for a big, heavily branched function (perform some checks and updates on table A, then perform some other updates on table B, C or D depending on column A.1, after the updates for B do something similat for F or G etc.); and still I wonder whether I should keep it split or revert to a single function.

EDIT: another case when this is useful is when some parts of the code are shared by several trigger functions. Then it might be useful to write the code just once and call the function instead of writing the whole code again. Again, it doesn't worth it for few lines of code as in your case.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.