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
add comment

1 Answer

up vote 1 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
add comment

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.