CYBERTEC Logo

What to return from a PostgreSQL row level trigger?

04.2021 / Category: / Tags: | |
writing a generic row-level trigger function is not that easy
© Laurenz Albe 2021

 

In this article, I'll talk about row level triggers, which are the most frequently used kind of triggers. I will describe what the return value of the trigger function means and suggest a useful code simplification.

Triggers in PostgreSQL

A trigger in PostgreSQL consists of two parts:

  • a trigger function
  • the actual trigger, which invokes the trigger function

This architecture has the advantage that trigger functions can be reused: triggers on different tables can use the same trigger function.

Trigger types

PostgreSQL has event triggers and triggers that get activated by data modifications. We will only consider the latter in this article.

There are statement level triggers (FOR EACH STATEMENT), but we will focus on the more common row level triggers (FOR EACH ROW). For such triggers, the trigger function runs once per modified table row.

Furthermore, triggers can run BEFORE, AFTER or INSTEAD OF the data modification that triggers it. INSTEAD OF triggers must be row level triggers on views.

The return value of a trigger function

Trigger functions are always declared as “RETURNS trigger”, but what you actually have to return is

  • for statement level triggers, the value NULL
  • for row level triggers, a row of the table on which the trigger is defined

The return value is ignored for row level AFTER triggers, so you may as well return NULL in that case. That leaves row level BEFORE triggers as the only interesting case.

In row level BEFORE triggers, the return value has the following meaning:

  • if the trigger returns NULL, the triggering operation is aborted, and the row will not be modified
  • for INSERT and UPDATE triggers, the returned row is the input for the triggering DML statement

Note also that you can have more than one row level BEFORE trigger on a table. In this case, the triggers are executed in the alphabetical order of their name, and the result of the previous trigger function becomes the input for the next trigger function.

NEW and OLD in row level triggers

The special variables NEW and OLD in a row level trigger function contain the new and the old row version. They can be modified and used in the RETURN statement.

Note that NEW is NULL in ON DELETE triggers and OLD is NULL in ON INSERT triggers.

trigger invocation NEW is set OLD is set
ON INSERT
ON UPDATE
ON DELETE

Example: an auditing row level trigger

We want to capture data modifications to a table mytab in mytab_hist:

Here we assume that id will never change; otherwise we would have to come up with something more complicated.

The trigger definition will look like

A possible trigger function could look like:

TG_OP contains the triggering event (INSERT, UPDATE, DELETE or TRUNCATE).

Simplification of the row level trigger function

Note how similar the code for both branches of the IF statement is. It would be nice to simplify that. Indeed the coalesce function makes it possible to write the same function in a much simpler fashion:

coalesce will return the first of its arguments that is not NULL. This does exactly the right thing in our case, because NEW is NULL in ON DELETE triggers.

Conclusion

Instead of writing complicated conditional code for the return value of a row level trigger, resort to the simple

which is almost always the right thing.

If you want to know more about triggers, you may want to read my article about constraint triggers.

0 0 votes
Article Rating
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Brent
Brent
11 months ago

This broke it down better than the official documentation.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    1
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram