CODEWITHSUNDEEP
postgresqltriggersplpgsql
Peter Krauss
Peter Krauss

Reputation: 13920

Can a function detect the trigger event type?

I am using a function in PostgreSQL 9.1:

CREATE FUNCTION myfunc() RETURNS trigger AS $$ ... $$ LANGUAGE plpgsql;

with a trigger:

CREATE TRIGGER mycheck
BEFORE INSERT OR UPDATE ON t
FOR EACH ROW EXECUTE PROCEDURE myfunc();

My problem now is to express a condition about events in the body of that function like (pseudocode):

IF TRIGGER_EVENT_WAS_INSERT THEN ...doThis... END IF;

How to express this condition?
(Note BEFORE INSERT OR UPDATE in the trigger!)

Upvotes: 8

Views: 4038

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656421

Yes, TG_OP. The manual:

TG_OP
Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.

Careful what you return in each case. Sometimes you want to RETURN NEW, which is not defined in case of a DELETE or vice versa. If it gets too complex, rather split into multiple triggers, called on separate events.

Example:

IF TG_OP = 'DELETE' THEN
   -- do something
   RETURN OLD;  -- depends!
ELSIF TG_OP = 'UPDATE' THEN  
   -- do something
   RETURN NEW;  -- depends!
END IF;

More code examples in related answers.

Upvotes: 23

Related Questions