A trigger in PostgreSQL

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;