Can a function detect the trigger event type?

Question

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!)

Hum... I am reading [pgdoc about plpgsql-trigger](http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html): I will try the `TG_OP` *special variable*. — Peter Krauss, May 19 '14 at 17:42

Erwin Brandstetter · Accepted Answer · 2021-10-24T16:51:55.833

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;

Can a function detect the trigger event type?

1 Answers1

Linked