A trigger in PostgreSQL
Tags: postgres, trigger, plpgsql, database
Published: 2024-01-10 16:20:01

[Buy Me a Coffee](ko-fi.com/superkai)☕

A [trigger](https://www.postgresql.org/docs/current/triggers.html): 

- 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](https://www.postgresql.org/docs/current/triggers.html) explains a trigger.
*[My post](https://dev.to/hyperkai/a-plpgsql-event-trigger-in-postgresql-23k8) explains an [event trigger](https://www.postgresql.org/docs/current/event-triggers.html).

For example, you create `person` table as shown below:

```sql
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:

```sql
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:

```sql
CREATE TABLE log (
  num INT
);
```

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

```sql
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:

```sql
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](https://stackoverflow.com/questions/7726237/postgresql-trigger-function-with-parameters).
  
  - must be called with a trigger otherwise there is error.

  - cannot be created with `LANGUAGE SQL` otherwise there is [the error](https://stackoverflow.com/questions/5565979/error-sql-functions-cannot-return-type-trigger-in-postgresql/77724247#77724247).

  - 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](https://www.postgresql.org/docs/current/plpgsql-trigger.html) explains a trigger function.

- [My post](https://dev.to/hyperkai/a-plpgsql-function-in-postgresql-3j9f) explains how to create a [PL/pgSQL function](https://www.postgresql.org/docs/current/plpgsql.html).

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:

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

Or:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

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

Or:

```sql
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:

```sql
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:

```sql
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:

```sql
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](https://dev.to/hyperkai/a-view-in-postgresql-2glb) explains a view:

```sql
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:

```sql
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](https://stackoverflow.com/questions/77762057/the-trigger-with-before-or-after-and-insert-update-or-delete-on-a-vie) explains it.

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

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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](https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html) as shown below:

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