Super Kai (Kazuya Ito)

Posted on Jan 10, 2024 • Edited on May 26, 2024

A trigger in PostgreSQL

#postgres #trigger #plpgsql #database

Buy Me a Coffee☕

A trigger:

can run the PL/pgSQL trigger function in the database. *A SQL trigger function and trigger procedure don't exist.
is fired when INSERT, UPDATE, DELETE or TRUNCATE operation happens or is prevented on a table or view in the database.

*The doc explains a trigger.
*My post explains an event trigger.

For example, you create person table as shown below:

CREATE TABLE person (
  id INT,
  first_name VARCHAR(20),
  last_name VARCHAR(20),
  age INT
);

Then, you insert 2 rows into person table as shown below:

INSERT INTO person (id, first_name, last_name, age) 
VALUES (1, 'John', 'Smith', 27), (2, 'David', 'Miller', 32);

Next, you create log table as shown below:

CREATE TABLE log (
  num INT
);

Then, you insert the row whose num is 0 into log table as shown below:

INSERT INTO log (num) VALUES (0);

Now, you can create my_func() trigger function with RETURNS trigger and LANGUAGE plpgsql which increments num by 1 as shown below:

CREATE FUNCTION my_func() RETURNS trigger
AS $$
BEGIN
  UPDATE log SET num = num + 1;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

*Memos:

An trigger function:
- must have no parameters otherwise there is the error.
- must be called with a trigger otherwise there is error.
- cannot be created with LANGUAGE SQL otherwise there is the error.
- must have trigger in RETURNS <type> clause to be used with an trigger otherwise there is error.
- must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.
A trigger procedure doesn't exist.
The doc explains a trigger function.
My post explains how to create a PL/pgSQL function.

And now, you can create my_t trigger which runs my_func() after INSERT or UPDATE operation happens on person table for each row as shown below:

CREATE TRIGGER my_t AFTER INSERT OR UPDATE ON person
FOR EACH ROW EXECUTE FUNCTION my_func();

Or:

CREATE TRIGGER my_t AFTER INSERT OR UPDATE ON person
FOR /* EACH */ ROW EXECUTE FUNCTION my_func();

*Memos:

You can set BEFORE instead of AFTER to run my_func() before UPDATE or INSERT operation happens on person table for each row. *You can also set INSTEAD OF which I explain later.
You can set one or more events INSERT, UPDATE and DELETE with FOR EACH ROW or FOR EACH STATEMENT for a function or view and TRUNCATE only with FOR EACH STATEMENT only for a function. *You can set multiple events with OR.
You can omit EACH which is optional so with and without EACH is the same.
Under my_func() returning NULL, if you set BEFORE with FOR EACH ROW, INSERT or UPDATE operation set on person table doesn't occur, then num is incremented by 1.

Then, you insert 2 rows to person table, then num is 2 as shown below:

postgres=# INSERT INTO person (id, first_name, last_name, age) VALUES (3, 'Robert', 'Wilson', 18), (4, 'Mark', 'Taylor', 40);
INSERT 0 2
postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | John       | Smith     |  27
  2 | David      | Miller    |  32
  3 | Robert     | Wilson    |  18
  4 | Mark       | Taylor    |  40
(4 rows)

postgres=# SELECT num FROM log;
 num
-----
   2
(1 row)

Then, you update first_name from Robert to Tom and last_name from Wilson to Brown where id is 3 on person table, then num is 3 as shown below:

postgres=# UPDATE person SET first_name = 'Tom', last_name = 'Brown' WHERE id = 3;
UPDATE 1
postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | John       | Smith     |  27
  2 | David      | Miller    |  32
  4 | Mark       | Taylor    |  40
  3 | Tom        | Brown     |  18
(4 rows)

postgres=# SELECT num FROM log;
 num
-----
   3
(1 row)

Then, you delete all 4 rows from person table, then num is still 3 as shown below:

postgres=# DELETE FROM person;
DELETE 4
postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
(0 rows)

postgres=# SELECT num FROM log;
 num
-----
   3
(1 row)

Next, you can create my_t trigger which runs my_func() before UPDATE or TRUNCATE operation happens on first_name or last_name on person table or on person table respectively for each statement as shown below:

CREATE TRIGGER my_t BEFORE UPDATE OF first_name, last_name OR TRUNCATE ON person
FOR EACH STATEMENT EXECUTE FUNCTION my_func();

Or:

CREATE TRIGGER my_t BEFORE UPDATE OF first_name, last_name OR TRUNCATE ON person
FOR /* EACH */ STATEMENT EXECUTE FUNCTION my_func();

*Memo:

Only UPDATE can specify one or more columns with OF while INSERT, DELETE and TRUNCATE cannot.
You can omit EACH which is optional so with and without EACH is the same.

Then, you update first_name to Tom on all 2 rows on person table, then num is 1 as shown below:

postgres=# UPDATE person SET first_name = 'Tom';
UPDATE 2
postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | Tom        | Smith     |  27
  2 | Tom        | Miller    |  32
(2 rows)

postgres=# SELECT num FROM log;
 num
-----
   1
(1 row)

Then, you update age to 50 on all 2 rows on person table, then num is still 1 as shown below:

postgres=# UPDATE person SET age = 50;
UPDATE 2
postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | Tom        | Smith     |  50
  2 | Tom        | Miller    |  50
(2 rows)

postgres=# SELECT num FROM log;
 num
-----
   1
(1 row)

Then, you truncate person table to delete all 2 rows from person table, then num is 2 as shown below:

postgres=# TRUNCATE TABLE person;
TRUNCATE TABLE
postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
(0 rows)

postgres=# SELECT num FROM log;
 num
-----
   2
(1 row)

Next, you create my_v view as shown below. *My post explains a view:

CREATE VIEW my_v AS
  SELECT * FROM person;

Now, you can create my_t trigger which runs my_func() when UPDATE or DELETE operation is prevented on my_view for each row as shown below:

CREATE TRIGGER my_t INSTEAD OF UPDATE OR DELETE ON my_v
FOR EACH ROW EXECUTE FUNCTION my_func();

*Memos:

INSTEAD OF can prevent operations.
You can use INSTEAD OF only with a view and FOR EACH ROW otherwise there is error.
You cannot specify one or more columns with UPDATE OF when using INSTEAD OF otherwise there is error.
You cannot use TRUNCATE with INSTEAD OF otherwise there is error.
The trigger with BEFORE or AFTER and INSERT, UPDATE or DELETE on a view doesn't work. *My question and the answers explains it.

Then, you insert a row to person table with my_v, then num is still 0 as shown below:

postgres=# INSERT INTO my_v (id, first_name, last_name, age) VALUES (3, 'Robert', 'Wilson', 18);
INSERT 0 1
postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | John       | Smith     |  27
  2 | David      | Miller    |  32
  3 | Robert     | Wilson    |  18
(3 rows)

postgres=# SELECT num FROM log;
 num
-----
   0
(1 row)

Then, you try to update first_name to Tom on all 3 rows on person table with my_v, then the update is prevented, then num is 3 as shown below:

postgres=# UPDATE my_v SET first_name = 'Tom';
UPDATE 0
postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | John       | Smith     |  27
  2 | David      | Miller    |  32
  3 | Robert     | Wilson    |  18
(3 rows)

postgres=# SELECT num FROM log;
 num
-----
   3
(1 row)

Then, you try to delete 2 rows from person table where id is 2 and 3 with my_v, then the deletion is prevented, then num is 5 as shown below:

postgres=# DELETE FROM my_v WHERE id IN (2, 3);
DELETE 0
postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | John       | Smith     |  27
  2 | David      | Miller    |  32
  3 | Robert     | Wilson    |  18
(3 rows)

postgres=# SELECT num FROM log;
 num
-----
   5
(1 row)

Next, you can use NEW and OLD records in func() trigger function to return the sum of age of NEW and OLD on person table as shown below:

CREATE FUNCTION my_func() RETURNS trigger
AS $$
BEGIN
  UPDATE log SET num = num + 1;
  NEW.age := NEW.age + OLD.age;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

*Memo:

NEW record is the new row which you are about to input.
OLD record is the old row which you have already input.
Basically, you should return NEW or OLD instead of NULL because there is the case INSERT, UPDATE, DELETE or TRUNCATE doesn't occur because NEW or OLD is not returned. *I explains the case later.
You can use = instead of :=.
Basically and initially for INSERT, NEW has a record while OLD has NULL.
Basically and initially for UPDATE, NEW and OLD have a record.
Basically and initially for DELETE, NEW has NULL while OLD has a record.
Always and initially for TRUNCATE, NEW and OLD have NULL.

Now, you can create my_t trigger which runs my_func() before UPDATE operation happens on person table for each row as shown below:

CREATE TRIGGER my_t BEFORE UPDATE ON person
FOR EACH ROW EXECUTE FUNCTION my_func();

*Memos:

In only case you set BEFORE and FOR EACH ROW to a trigger, you can change the actual record of INSERT or UPDATE by returning NEW or OLD which has a record. *Initially, NULL or a record is passed to NEW or OLD.
If my_func() returns NULL or returns NEW or OLD which has NULL and you set BEFORE with FOR EACH ROW to a trigger, the operation INSERT, UPDATE or DELETE which you set to a trigger doesn't occur, then num is incremented by 1. *Initially, NULL or a record is passed to NEW or OLD.
Whether or not my_func() returns NULL or returns NEW or OLD which has a record and if you set BEFORE with FOR EACH STATEMENT to a trigger, the operation INSERT, UPDATE, DELETE or TRUNCATE works with the actual record which you input without any modification, then num is incremented by 1. *Initially, NULL or a record is passed to NEW or OLD.
Whether or not my_func() returns NULL or returns NEW or OLD which has a record and if you set AFTER with FOR EACH ROW to a trigger, the operation INSERT, UPDATE or DELETE works with the actual record which you input without any modification, then num is incremented by 1. *Initially, NULL or a record is passed to NEW or OLD.
Whether or not my_func() returns NULL or returns NEW or OLD which has a record and if you set AFTER with FOR EACH STATEMENT to a trigger, the operation INSERT, UPDATE, DELETE or TRUNCATE works with the actual record which you input without any modification, then num is incremented by 1. *Initially, only NULL is passed to NEW and OLD.
Whether or not my_func() returns NULL or returns NEW or OLD which has a record and if you set INSTEAD OF with FOR EACH ROW to a trigger, the operation INSERT, UPDATE or DELETE works with the actual record which you input without any modification, then num is incremented by 1. *Initially, NULL or a record is passed to NEW or OLD.

Then, you update age to 68 where id is 2 on person table, then age where id is 2 is 100 and num is 1 as shown below:

postgres=# UPDATE person SET age = 68 WHERE id = 2;
UPDATE 1
postgres=# SELECT * FROM person;
 id | first_name | last_name | age
----+------------+-----------+-----
  1 | John       | Smith     |  27
  2 | David      | Miller    | 100
(2 rows)

postgres=# SELECT num FROM log;
 num
-----
   1
(1 row)

In addition, you can check if NEW or OLD has a record or NULL with RAISE statement as shown below:

CREATE FUNCTION my_func() RETURNS trigger
AS $$
BEGIN
  RAISE INFO 'NEW:%, OLD:%', NEW, OLD;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DEV Community

A trigger in PostgreSQL

Top comments (0)