Create a trigger on a table that fires on UPDATE, DELETE, or INSERT

Question

In Postgres, do you have to create a new trigger for each event, or is there a way to combine them and have the trigger fire whenever data is inserted, deleted or updated on a table?

Yes. See Example 43.4. A PL/pgSQL Trigger Function for Auditing here plpgsql trigger. Also statement diagram here CREATE TRIGGER. — Adrian Klaver
– Adrian Klaver, Commented Apr 18, 2023 at 14:48
One trigger and one trigger function could be enough to handle all you events. But it depends on your requirements, there is for example a difference between a BEFORE and AFTER. If you need them both, you need two triggers and at least one trigger function. — Frank Heikens
– Frank Heikens, Commented Apr 18, 2023 at 14:49
@FrankHeikens, no you don't. You can have one trigger that covers multiple actions. — Adrian Klaver
– Adrian Klaver, Commented Apr 18, 2023 at 14:49
@AdrianKlaver: I don't fully agree because some data is just not available. And Yes, these are exceptions, but they do occur and this is also why there is a difference between the two. — Frank Heikens
– Frank Heikens, Commented Apr 18, 2023 at 14:52
Alright I skipped over the BEFORE/AFTER situation. In that case there needs to be a trigger for each. — Adrian Klaver
– Adrian Klaver, Commented Apr 18, 2023 at 14:57

Erwin Brandstetter · Accepted Answer · 2023-04-18 14:55:31Z

1

Yes. The syntax is like:

CREATE TRIGGER my_trigger
AFTER INSERT OR UPDATE OR DELETE ON my_tbl
FOR EACH ROW
EXECUTE FUNCTION my_trigger_func();

Before Postgres 11 the (misleading) keyword was PROCEDURE instead of FUNCTION, which is still accepted in Postgres 15 (and probably for much longer).

There are many syntax variants. Details in the manual here and here.

answered Apr 18, 2023 at 14:46

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Blankman Over a year ago

so inside of my function, how do I know if it was an update, delete or insert? It is a AFTER trigger, and my function currently references NEW.product_id.

Frank Heikens Over a year ago

@Blankman: See TG_OP Please read the manual, all information is there, including examples: postgresql.org/docs/current/plpgsql-trigger.html

Erwin Brandstetter Over a year ago

@Blankman: Follow the provided link to my answer that addresses this question exactly. And read the manual. Note that NEW is not defined in case of a DELETE.

Blankman Over a year ago

If NEW exists, but the column is null, can I safely do a NEW.product_id IS NOT NULL check?

Erwin Brandstetter Over a year ago

Yes, NEW is a record variable. If assigned, you can do that. Except for (rare) statement level triggers. My previous comment was not precise. Since Postgres 10, when not applicable, NEW & OLD are NULL rather than unassigned. Either way, switch cases based on TG_OP in the trigger function or write separate functions and triggers. Here is another example.

Collectives™ on Stack Overflow

Create a trigger on a table that fires on UPDATE, DELETE, or INSERT

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related