0

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Blankman
  • 259,732
  • 324
  • 769
  • 1,199
  • 1
    Yes. See *Example 43.4. A PL/pgSQL Trigger Function for Auditing* here [plpgsql trigger](https://www.postgresql.org/docs/current/plpgsql-trigger.html). Also statement diagram here [CREATE TRIGGER](https://www.postgresql.org/docs/current/sql-createtrigger.html). – Adrian Klaver Apr 18 '23 at 14:48
  • 2
    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 Apr 18 '23 at 14:49
  • @FrankHeikens, no you don't. You can have one trigger that covers multiple actions. – Adrian Klaver Apr 18 '23 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 Apr 18 '23 at 14:52
  • Alright I skipped over the BEFORE/AFTER situation. In that case there needs to be a trigger for each. – Adrian Klaver Apr 18 '23 at 14:57

1 Answers1

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.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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`. – Blankman Apr 18 '23 at 15:08
  • 1
    @Blankman: See TG_OP Please read the manual, all information is there, including examples: https://www.postgresql.org/docs/current/plpgsql-trigger.html – Frank Heikens Apr 18 '23 at 15:10
  • 1
    @Blankman: Follow the [provided link](https://stackoverflow.com/a/23744320/939860) to my answer that addresses this question exactly. And read the manual. Note that `NEW` is not defined in case of a `DELETE`. – Erwin Brandstetter Apr 18 '23 at 15:22
  • If NEW exists, but the column is null, can I safely do a `NEW.product_id IS NOT NULL` check? – Blankman Apr 18 '23 at 16:10
  • Yes, `NEW` is a record variable. If assigned, you can do that. Except for (rare) [statement level triggers](https://stackoverflow.com/a/24195624/939860). 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](https://stackoverflow.com/a/49566232/939860). – Erwin Brandstetter Apr 18 '23 at 16:40