Table of Contents
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.
A trigger in PostgreSQL consists of two parts:
This architecture has the advantage that trigger functions can be reused: triggers on different tables can use the same trigger function.
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.
Trigger functions are always declared as “RETURNS trigger
”, but what you actually have to return is
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:
INSERT
and UPDATE
triggers, the returned row is the input for the triggering DML statementNote 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 triggersThe 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 |
✓ |
We want to capture data modifications to a table mytab
in mytab_hist
:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE mytab ( id bigint GENERATED ALWAYS AS IDENTITY NOT NULL, val text ); CREATE TABLE mytab_hist ( mod_time timestamp with time zone DEFAULT clock_timestamp() NOT NULL, operation text NOT NULL, id bigint NOT NULL, val text, PRIMARY KEY (id, mod_time) ); |
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
1 2 3 |
CREATE TRIGGER mytab_hist BEFORE INSERT OR UPDATE OR DELETE ON mytab FOR EACH ROW EXECUTE FUNCTION mytab_hist(); |
A possible trigger function could look like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE FUNCTION mytab_hist() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF TG_OP = 'DELETE' THEN INSERT INTO mytab_hist (operation, id, val) VALUES (TG_OP, OLD.id, NULL); RETURN OLD; ELSE INSERT INTO mytab_hist (operation, id, val) VALUES (TG_OP, NEW.id, NEW.val); RETURN NEW; END IF; END;$$; |
TG_OP
contains the triggering event (INSERT
, UPDATE
, DELETE
or TRUNCATE
).
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:
1 2 3 4 5 6 7 8 |
CREATE OR REPLACE FUNCTION mytab_hist() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN INSERT INTO mytab_hist (operation, id, val) VALUES (TG_OP, coalesce(NEW.id, OLD.id), NEW.val); RETURN coalesce(NEW, OLD); END;$$; |
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.
Instead of writing complicated conditional code for the return value of a row level trigger, resort to the simple
1 |
RETURN coalesce(NEW, OLD); |
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.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
This broke it down better than the official documentation.