# Lesson 17: PL/pgSQL / Triggers

> Native Markdown source page: PL/pgSQL / Triggers

---

LLMS index: [llms.txt](/en/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references.
> **HTML source:** [dev1_17_plpgsql_triggers.html](https://edu.postgrespro.com/16/dev1-16-en/dev1_17_plpgsql_triggers.html)
> **Archive:** [DEV1-handouts-16-en.zip](https://edu.postgrespro.com/16/DEV1-handouts-16-en.zip)
> **PDF pages:** 28

## Page 1: PL/pgSQL / Triggers {#source-page-001}

### Copyright

© Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova Photo by: Oleg Bartunov (Phu monastery, Bhrikuti summit, Nepal)
### Use of Course Materials

Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials.
### Feedback

Please send your feedback, comments and suggestions to: edu@postgrespro.ru
### Disclaimer

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications.

## Page 2: Topics {#source-page-002}

- Triggers and Trigger Functions
- Triggering
- Execution Context of a Trigger Function
- Return Values
- Do’s and Don’ts
- Event Triggers

## Page 3: Triggers and Functions {#source-page-003}

**Trigger**
  - a database object: a list of events to process
  - once an event occurs, the trigger function is called,
  - and the call context is passed to the function
- Trigger function
  - a database object: event processing code
  - executed in the same transaction as the main operation
  - convention: the function does not take any parameters,
  - returns a value of the trigger pseudotype (which is virtually record)
  - can be reused with multiple triggers

### Speaker notes

Triggers are used to set off particular actions in response to particular events. A trigger consists of two parts: the trigger itself (which defines the events) and a trigger function (which defines the actions). Both the trigger and the function are independent database objects.

When an event occurs which the trigger is waiting for, the trigger function is called. It receives the context of the call, which defines the exact trigger that has called the function and the exact conditions that have led to this call.

A trigger function is a regular function that follows some conventions:

● It can be written in any language except pure SQL.

● It must have no parameters.

● Its return value is of the trigger type (which is actually a pseudotype; a record corresponding to a table row is returned instead).

The trigger function is executed in the same transaction as the main operation. Thus, if a trigger function results in an error, the whole transaction is aborted.

<https://postgrespro.com/docs/postgresql/16/trigger-definition>

## Page 4: Events {#source-page-004}

INSERT, UPDATE, DELETE
  - tables
  - before/after statement
  - before/after row
  - views
  - before/after statement
  - instead of
  - row
- TRUNCATE
  - tables
  - before/after statement
- WHEN condition
  - sets an additional filter

### Speaker notes

Triggers can fire for INSERT, UPDATE, or DELETE operations performed on tables or views, as well as for the TRUNCATE operation on tables.

A trigger can fire before the specified action (BEFORE), after the action (AFTER), or instead of the action (INSTEAD OF).

A trigger can fire once for the whole operation (FOR EACH STATEMENT) or for each affected row (FOR EACH ROW).

There are some combinations of these conditions that are not supported. For example, INSTEAD OF triggers can be defined only for views at the row level, while TRUNCATE triggers can be defined only for tables and only at the statement level. Possible combinations are listed on this slide.

Besides, you can limit the scope controlled by the trigger by specifying the WHEN condition: if this condition is not true, the trigger does not fire.

<https://postgrespro.com/docs/postgresql/16/sql-createtrigger>

## Page 5: Before Statement {#source-page-005}

**Triggers**
  - BEFORE STATEMENT
  - before the operation
- Return value
  - operation execution
  - is ignored
- Context
  - TG variables

### Speaker notes

Let’s take a closer look at different trigger types.

The BEFORE STATEMENT trigger fires only once per operation, regardless of the number of affected rows (even if there are none). It happens before the start of the operation.

The return value of the trigger function is ignored. If there is an error in the trigger, the operation is canceled. Since the trigger function has no parameters, the call context in PL/pgSQL is passed via predefined TG variables, such as:

● TG\_WHEN = BEFORE

● TG\_LEVEL = STATEMENT

● TG\_OP = INSERT/UPDATE/DELETE/TRUNCATE

etc. You can also pass a user-defined context (which is analogous to the absent parameters) via the TG\_ARGV variable, although it is usually advisable to create several specialized functions instead of a single generic one.

<https://postgrespro.com/docs/postgresql/16/plpgsql-trigger>

## Page 6: Before Row {#source-page-006}

**Triggers**
  - BEFORE STATEMENT
  - before the action on the row is taken
  - during the statement execution
  - operation execution
- Return value
  - BEFORE ROW
  - a row (possibly modified)
  - null cancels the action
- Context
  - OLD
  - update, delete
  - NEW
  - insert, update
  - TG variables

### Speaker notes

BEFORE ROW triggers fire each time an operation is about to process a row. It happens right during the operation execution.

Trigger functions get the context via variables, such as:

● OLD — an old state of the row (undefined for insertion)

● NEW — an updated state of the row (undefined for deletion)

● TG\_WHEN = BEFORE

● TG\_LEVEL = ROW

● TG\_OP = INSERT/UPDATE/DELETE

etc.

The NULL return value is interpreted as cancellation of the action for the current row. The execution of the operation itself will continue, but the current row won’t be processed, and other triggers won’t fire for this row.

To avoid interfering with the operation, the trigger must return the received row without any modifications, so it must always return NEW for insert and update operations. For delete operations, it can return any value except NULL (usually OLD is used).

But the trigger function can also change the NEW value to affect the result of the operation; this trigger is often defined exactly for this purpose.

## Page 7: Instead of Row {#source-page-007}

**Triggers**
  - BEFORE STATEMENT
  - instead of the action on the row
  - for views
  - operation execution
- Return value
  - BEFORE ROW
  - INSTEAD OF ROW
  - a row (possibly modified)
  - is shown in RETURNING
  - null cancels the action
- Context
  - OLD
  - update, delete
  - NEW
  - insert, update
  - TG variables

### Speaker notes

INSTEAD OF triggers are very similar to BEFORE triggers, but they can be defined only for views and fire instead of the specified operation, not before it.

Such triggers usually perform operations on the base tables for views. The trigger can also return a modified NEW value: this value will be available when performing an operation with the RETURNING clause.

## Page 8: After Row {#source-page-008}

**Triggers**
  - BEFORE STATEMENT
  - after operation execution
  - queue of rows satisfying the WHEN condition
  - operation execution
- Return value
  - BEFORE ROW
  - INSTEAD OF ROW
  - is ignored
- Context
  - OLD, OLD TABLE
  - update, delete
  - NEW, NEW TABLE insert, update
  - TG variables
  - AFTER ROW

### Speaker notes

Just as BEFORE ROW, AFTER ROW triggers fire for each affected row; but it happens only after the whole operation is complete, not immediately after processing the row. This ensures that when these triggers access the table being modified, the result does not depend on the order of row processing. For this purpose, all events are placed in a queue and processed after the operation has finished. The fewer events get queued, the smaller overhead will be incurred; that’s why it is recommended to use the WHEN clause in this case, which can filter out the rows that we definitely won't need.

The return value of AFTER ROW triggers is ignored (because the operation is already complete).

The context of the trigger function is constituted by the following variables:

- OLD — an old state of the row (undefined for insertion)

- NEW — an updated value of the row (undefined for deletion)

Apart from these variables, the trigger function can access special transition tables. The table specified as OLD TABLE when creating the trigger contains the old values of the rows processed by the trigger, and the NEW TABLE contains the new values of the same rows.

Regular TG variables are also available, including the following ones:

● TG\_WHEN = AFTER

● TG\_LEVEL = ROW

● TG\_OP = INSERT/UPDATE/DELETE

## Page 9: After Statement {#source-page-009}

**Triggers**
  - BEFORE STATEMENT
  - after the operation
  - (even if none of the rows are affected)
  - operation execution
  - BEFORE ROW
  - INSTEAD OF ROW
- Return value
  - is ignored
- Context
  - OLD TABLE
  - update, delete
  - NEW TABLE
  - insert, update
  - TG variables
  - AFTER ROW
  - AFTER STATEMENT

### Speaker notes

The AFTER STATEMENT trigger fires after the operation has completed (including all the AFTER ROW triggers, if any). This trigger fires only once regardless of the number of the affected rows.

The return value of the trigger function is ignored.

The call context is passed using transition tables. The trigger function can access these tables to analyze all the affected rows. Transition tables are usually used with AFTER STATEMENT, not with AFTER ROW triggers.

Besides, regular TG variables are defined, such as:

● TG\_WHEN = AFTER

● TG\_LEVEL = STATEMENT

● TG\_OP = INSERT/UPDATE/DELETE/TRUNCATE

etc.

## Page 10: Trigger Firing Order {#source-page-010}

Let’s create a “universal” trigger function that describes the context in which it is called. The context is passed in various TG variables.
We are going to define triggers for various events and observe the order in which the triggers are fired during execution.
```sql
=> CREATE OR REPLACE FUNCTION describe() RETURNS trigger
AS $$
DECLARE
    rec record;
    str text := '';
BEGIN
    IF TG_LEVEL = 'ROW' THEN
        CASE TG_OP
            WHEN 'DELETE' THEN rec := OLD; str := OLD::text;
            WHEN 'UPDATE' THEN rec := NEW; str := OLD || ' -> ' || NEW;
            WHEN 'INSERT' THEN rec := NEW; str := NEW::text;
        END CASE;
    END IF;
    RAISE NOTICE '% % % %: %',
        TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, str;
    RETURN rec;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
A table:
```sql
=> CREATE TABLE t(
    id integer PRIMARY KEY,
    s text
);
```
```sql
CREATE TABLE
```
Triggers at the statement level:
```sql
=> CREATE TRIGGER t_before_stmt
BEFORE INSERT OR UPDATE OR DELETE -- events
ON t                              -- table
FOR EACH STATEMENT                -- level
EXECUTE FUNCTION describe();      -- trigger function
```
```sql
CREATE TRIGGER
```
```sql
=> CREATE TRIGGER t_after_stmt
AFTER INSERT OR UPDATE OR DELETE ON t
FOR EACH STATEMENT EXECUTE FUNCTION describe();
```
```sql
CREATE TRIGGER
```
Triggers at the row level:
```sql
=> CREATE TRIGGER t_before_row
BEFORE INSERT OR UPDATE OR DELETE ON t
FOR EACH ROW EXECUTE FUNCTION describe();
```
```sql
CREATE TRIGGER
```
```sql
=> CREATE TRIGGER t_after_row
AFTER INSERT OR UPDATE OR DELETE ON t
FOR EACH ROW EXECUTE FUNCTION describe();
```
```sql
CREATE TRIGGER
```
Let’s perform an INSERT:
```sql
=> INSERT INTO t VALUES (1,'aaa'), (2, 'bbb');
```
```sql
NOTICE:  t BEFORE INSERT STATEMENT:
NOTICE:  t BEFORE INSERT ROW: (1,aaa)
NOTICE:  t BEFORE INSERT ROW: (2,bbb)
NOTICE:  t AFTER INSERT ROW: (1,aaa)
NOTICE:  t AFTER INSERT ROW: (2,bbb)
NOTICE:  t AFTER INSERT STATEMENT:
INSERT 0 2
```

## Page 11: Page 11 {#source-page-011}

Now run an UPDATE:
```sql
=> UPDATE t SET s = 'ccc' WHERE id = 1;
```
```sql
NOTICE:  t BEFORE UPDATE STATEMENT:
NOTICE:  t BEFORE UPDATE ROW: (1,aaa) -> (1,ccc)
NOTICE:  t AFTER UPDATE ROW: (1,aaa) -> (1,ccc)
NOTICE:  t AFTER UPDATE STATEMENT:
UPDATE 1
```
Statement-level triggers will fire even if the command has not processed any rows at all:
```sql
=> UPDATE t SET s = 'ddd' WHERE id = 0;
```
```sql
NOTICE:  t BEFORE UPDATE STATEMENT:
NOTICE:  t AFTER UPDATE STATEMENT:
UPDATE 0
```
Here is a subtle point: the INSERT statement with the ON CONFLICT clause activates BEFORE triggers both on insert and update:
```sql
=> INSERT INTO t VALUES (1,'ddd'), (3,'eee')
ON CONFLICT(id) DO UPDATE SET s = EXCLUDED.s;
```
```sql
NOTICE:  t BEFORE INSERT STATEMENT:
NOTICE:  t BEFORE UPDATE STATEMENT:
NOTICE:  t BEFORE INSERT ROW: (1,ddd)
NOTICE:  t BEFORE UPDATE ROW: (1,ccc) -> (1,ddd)
NOTICE:  t BEFORE INSERT ROW: (3,eee)
NOTICE:  t AFTER UPDATE ROW: (1,ccc) -> (1,ddd)
NOTICE:  t AFTER INSERT ROW: (3,eee)
NOTICE:  t AFTER UPDATE STATEMENT:
NOTICE:  t AFTER INSERT STATEMENT:
INSERT 0 2
```
And finally, let’s try out a DELETE:
```sql
=> DELETE FROM t WHERE id = 2;
```
```sql
NOTICE:  t BEFORE DELETE STATEMENT:
NOTICE:  t BEFORE DELETE ROW: (2,bbb)
NOTICE:  t AFTER DELETE ROW: (2,bbb)
NOTICE:  t AFTER DELETE STATEMENT:
DELETE 1
```
There is no dedicated trigger type for the MERGE statement (introduced in PostgreSQL 15), use regular triggers for UPDATE, INSERT, DELETE:
```sql
=> MERGE INTO t
USING (VALUES (1, 'fff'), (3, 'ggg'), (4, 'hhh')) AS vals(id, s)
ON t.id = vals.id
WHEN MATCHED AND t.id = 1 THEN
  UPDATE SET s = vals.s
WHEN MATCHED THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (id, s)
  VALUES (vals.id, vals.s);
```
```sql
NOTICE:  t BEFORE INSERT STATEMENT:
NOTICE:  t BEFORE UPDATE STATEMENT:
NOTICE:  t BEFORE DELETE STATEMENT:
NOTICE:  t BEFORE UPDATE ROW: (1,ddd) -> (1,fff)
NOTICE:  t BEFORE DELETE ROW: (3,eee)
NOTICE:  t BEFORE INSERT ROW: (4,hhh)
NOTICE:  t AFTER UPDATE ROW: (1,ddd) -> (1,fff)
NOTICE:  t AFTER DELETE ROW: (3,eee)
NOTICE:  t AFTER INSERT ROW: (4,hhh)
NOTICE:  t AFTER DELETE STATEMENT:
NOTICE:  t AFTER UPDATE STATEMENT:
NOTICE:  t AFTER INSERT STATEMENT:
MERGE 3
```
### Transition Tables
Let’s create a trigger function that shows the contents of transition tables. Here we use old\_table and new\_table names: they will be declared as part of the trigger definition.
Transition tables look just like regular ones, but they are not included into the system catalog and are located in RAM (although

## Page 12: Page 12 {#source-page-012}

they can be flushed to disk if they get too large).
```sql
=> CREATE OR REPLACE FUNCTION transition() RETURNS trigger
AS $$
DECLARE
    rec record;
BEGIN
    IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
        RAISE NOTICE 'Old state:';
        FOR rec IN SELECT * FROM old_table LOOP
            RAISE NOTICE '%', rec;
        END LOOP;
    END IF;
    IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
        RAISE NOTICE 'New state:';
        FOR rec IN SELECT * FROM new_table LOOP
            RAISE NOTICE '%', rec;
        END LOOP;
    END IF;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Let’s create a new table:
```sql
=> CREATE TABLE trans(
    id integer PRIMARY KEY,
    n integer
);
```
```sql
CREATE TABLE
```
```sql
=> INSERT INTO trans VALUES (1,10), (2,20), (3,30);
```
```sql
INSERT 0 3
```
To create transition tables for an operation, you have to specify their names in the trigger definition:
```sql
=> CREATE TRIGGER t_after_upd_trans
AFTER UPDATE ON trans -- one event per trigger
REFERENCING
    OLD TABLE AS old_table -- it is OK to specify only one table,
    NEW TABLE AS new_table -- there is no need to provide both
FOR EACH STATEMENT
EXECUTE FUNCTION transition();
```
```sql
CREATE TRIGGER
```
Let’s check the result:
```sql
=> UPDATE trans SET n = n + 1 WHERE n <= 20;
```
```sql
NOTICE:  Old state:
NOTICE:  (1,10)
NOTICE:  (2,20)
NOTICE:  New state:
NOTICE:  (1,11)
NOTICE:  (2,21)
UPDATE 2
```
Transition tables contain only those rows that have been affected by the operation.
In addition to updates, transition tables are also supported for INSERT and DELETE operations, although only one table will be available at a time: OLD TABLE is unavailable for inserts, while NEW TABLE is unavailable for deletes.
Since AFTER ROW triggers fire after the whole operation is completed, they can also use transition tables. But there is usually no point in it.

## Page 13: Possible Use Cases {#source-page-013}

  - operation applicability checks
- BEFORE STATEMENT
  - validation,
  - row modifications
- operation execution
- BEFORE ROW
  - changing the base tables for views
- INSTEAD OF ROW
  - consistency checks,
  - including table-level checks;
  - logging operations for audit purposes;
  - cascading table updates
  - (denormalization, asynchronous
  - processing...)
- AFTER ROW
- AFTER STATEMENT

### Speaker notes

What are triggers actually used for?

BEFORE triggers can be used to check if the operation is valid and to raise errors if required.

BEFORE ROW triggers can be used to modify a row (for example, fill an empty field with the required value). It is convenient to use such triggers to avoid repeating the logic of filling out “technical” fields in each operation, as well as tweak the application behavior if its code cannot be modified.

INSTEAD OF ROW triggers are used to translate operations on views into the corresponding operations on the underlying base tables.

AFTER ROW and AFTER STATEMENT triggers can be useful for getting the exact state after the operation (BEFORE triggers may affect the result, so the state is not yet clear at this stage):

● To check consistency of the operation.

● To perform audit operations, i.e., logging all changes in a separate storage.

● To cascade changes to other tables (for example, to update denormalized data if the base tables have changed, or queue changes for subsequent processing outside of the current transaction).

If the operation affects multiple rows, it may be more efficient to use AFTER STATEMENT on transition tables instead of AFTER ROW as it can process changes in a batch.

## Page 14: Challenges {#source-page-014}

**The code is called implicitly**
  - the execution logic is hard to track
- Visibility rules for volatile trigger functions
  - the result of BEFORE ROW and INSTEAD OF ROW triggers is visible
- The order of calling triggers for one and the same event
  - triggers fire in the alphabetical order
- Infinite looping can occur
  - a trigger can activate other triggers
- Integrity constraints can be broken
  - for example, by excluding rows that have to be deleted

### Speaker notes

Triggers should not be overused. As they fire implicitly, the logic of the application becomes obscure, thus making its maintenance hugely complicated. Attempts to use triggers for implementing complex logic are usually quite unfortunate.

In some cases, you can use generated columns instead of triggers (GENERATED ALWAYS AS ... STORED). If applicable, this solution is sure to be more transparent and easier to implement.

There is a number of subtle points related to using triggers; we consciously skip their detailed discussion here:

● Visibility rules of volatile functions in BEFORE ROW and INSTEAD OF ROW triggers (do not rely on the order of triggers when accessing a table)

● The order of calling several triggers on one and the same event (do not aggravate implicit firing of triggers by relying on their exact processing sequence)

● A possibility of infinite looping if cascade firing of triggers leads to another activation of the first trigger

● A risk of integrity constraint violation (for example, referential integrity can be compromised when skipping a row deleted by the ON DELETE CASCADE condition)

If you see that these subtleties are important for your application, you should seriously consider redesigning it.

## Page 15: Examples of Using Triggers {#source-page-015}

Example 1: saving the history of row changes.
Suppose we have a table that contains the current data. The task is to save the main table’s history of all row changes into a separate table.
Historical table support could be delegated to the application, but chances are high that some part of the history won’t be saved if an error occurs. That’s why we are going to solve this problem using triggers.
As an example, we will create two tables: one with actual, up-to-date data, and the other with historical data tracking all changes made to the first table.
The main table will store various British coins and the materials they are made of:
```sql
=> CREATE TABLE coins(
    name text PRIMARY KEY,
    material text
);
```
```sql
CREATE TABLE
```

## Page 16: Event Triggers {#source-page-016}

**Event trigger**
  - is similar to a regular trigger defined on a table, but is a separate object
- Trigger function
  - convention: a function does not take any parameters,
  - returns a value of the event\_trigger pseudotype
  - the call context is retrieved using special functions
- Events
  - DDL\_COMMAND\_START
  - before command execution
  - DDL\_COMMAND\_END
  - after command execution
  - TABLE\_REWRITE
  - before rewriting the table
  - SQL\_DROP
  - after deleting objects

### Speaker notes

Event triggers are virtually the same as regular triggers, but instead of firing on DML operations, they fire on DDL operations (CREATE, ALTER, DROP, COMMENT, GRANT, REVOKE).

Such triggers are not an application development tool; they are mainly used for database administration purposes. We only mention them here to give you a complete picture, so we’ll provide a very basic example.

<https://postgrespro.com/docs/postgresql/16/event-trigger-definition>

<https://postgrespro.com/docs/postgresql/16/event-trigger-matrix>

<https://postgrespro.com/docs/postgresql/16/sql-createeventtrigger>

<https://postgrespro.com/docs/postgresql/16/functions-event-triggers>

## Page 17: Page 17 {#source-page-017}


## Page 18: Takeaways {#source-page-018}

**A trigger is a way to address a particular event**
- Using triggers, you can cancel an operation, modify its outcome, or perform additional actions
- Triggers are executed as part of the main transaction; an error in a trigger aborts this transaction
- Using AFTER ROW triggers and transition tables makes processing more expensive
- Everything is good in moderation: complex logic is hard to debug because of implicit trigger execution

## Page 19: Practice {#source-page-019}

1. Create a trigger that handles updates of the onhand\_qty field in the catalog\_v view.
- Check that the Catalog tab now allows ordering books. 2. Make sure that the following consistency requirement is met: the amount of available books cannot be negative (it is impossible to buy a book if it is not in stock).
  - Check your implementation carefully, keeping in mind that the
  - application can be accessed by several users simultaneously.

### Speaker notes

2. It may seem that it’s enough to define the AFTER trigger on the operations table to calculate the qty\_change sum. However, at the READ COMMITTED isolation level used in the Bookstore application, we will have to acquire an exclusive lock on this table: otherwise, the check may not function properly in some scenarios.

Here is a better approach: extend the books table with the onhand\_qty column and create a trigger that will be modifying onhand\_qty values when the operations table is changed (i.e., you should virtually perform data denormalization). You can now define the CHECK constraint on the onhand\_qty field to ensure data consistency. The onhand\_qty() function created earlier is no longer required.

You should pay special attention to setting the initial value, keeping in mind that the database system may be serving some users while we apply these changes.

## Page 20: 1. Using a Trigger to Update the Catalog {#source-page-020}

```sql
=> CREATE FUNCTION update_catalog() RETURNS trigger
AS $$
BEGIN
    INSERT INTO operations(book_id, qty_change) VALUES
        (OLD.book_id, NEW.onhand_qty - coalesce(OLD.onhand_qty,0));
    RETURN NEW;
END
$$ VOLATILE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> CREATE TRIGGER update_catalog_trigger
INSTEAD OF UPDATE ON catalog_v
FOR EACH ROW
EXECUTE FUNCTION update_catalog();
```
```sql
CREATE TRIGGER
```
### 2. Checking the Quantity of Books
Let’s extend the table with the column that will store the quantity of available books.
```sql
=> ALTER TABLE books ADD COLUMN onhand_qty integer;
```
```sql
ALTER TABLE
```
The trigger function for the AFTER trigger, which is fired on insertion to update the quantity of available books (we assume that the onhand\_qty field cannot be empty):
```sql
=> CREATE FUNCTION update_onhand_qty() RETURNS trigger
AS $$
BEGIN
    UPDATE books
    SET onhand_qty = onhand_qty + NEW.qty_change
    WHERE book_id = NEW.book_id;
    RETURN NULL;
END
$$ VOLATILE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
The remaining operations are performed within a single transaction.
```text
=> BEGIN;
```
```text
BEGIN
```
Locking the table for the duration of the transaction:
```text
=> LOCK TABLE operations;
```
```text
LOCK TABLE
```
Providing the initial value:
```sql
=> UPDATE books b
SET onhand_qty = (
    SELECT coalesce(sum(qty_change),0)
    FROM operations o
    WHERE o.book_id = b.book_id
);
```
```sql
UPDATE 7
```
Defining constraints now that the field is non-empty:
```sql
=> ALTER TABLE books ALTER COLUMN onhand_qty SET DEFAULT 0;
```
```sql
ALTER TABLE
```
```sql
=> ALTER TABLE books ALTER COLUMN onhand_qty SET NOT NULL;
```
```sql
ALTER TABLE
```
```sql
=> ALTER TABLE books ADD CHECK(onhand_qty >= 0);
```
```sql
ALTER TABLE
```

## Page 21: Page 21 {#source-page-021}

Creating a trigger:
```sql
=> CREATE TRIGGER update_onhand_qty_trigger
AFTER INSERT ON operations
FOR EACH ROW
EXECUTE FUNCTION update_onhand_qty();
```
```sql
CREATE TRIGGER
```
Done.
```text
=> COMMIT;
```
```text
COMMIT
```
Now the books.onhand\_qty column is being updated, but the catalog\_v view still calls a function to calculate the number of books. Although the syntax used for function access in the initial query is the same as that for the field access, the query has been stored in a different form:
```sql
=> \d+ catalog_v
```
```sql
                            View "bookstore.catalog_v"
    Column    |  Type   | Collation | Nullable | Default | Storage  | Description
--------------+---------+-----------+----------+---------+----------+-------------
 book_id      | integer |           |          |         | plain    |
 title        | text    |           |          |         | extended |
 onhand_qty   | integer |           |          |         | plain    |
 display_name | text    |           |          |         | extended |
 authors      | text    |           |          |         | extended |
View definition:
 SELECT book_id,
    title,
    onhand_qty(b.*) AS onhand_qty,
    book_name(book_id, title) AS display_name,
    authors(b.*) AS authors
   FROM books b
  ORDER BY (book_name(book_id, title));
Triggers:
    update_catalog_trigger INSTEAD OF UPDATE ON catalog_v FOR EACH ROW EXECUTE FUNCTION
update_catalog()
```
Let’s replace the view:
```sql
=> CREATE OR REPLACE VIEW catalog_v AS
SELECT b.book_id,
       b.title,
       b.onhand_qty,
       book_name(b.book_id, b.title) AS display_name,
       b.authors
FROM   books b
ORDER BY display_name;
```
```sql
CREATE VIEW
```
Now the function can be deleted.
```sql
=> DROP FUNCTION onhand_qty(books);
```
```sql
DROP FUNCTION
```
Let’s run a small check:
```sql
=> SELECT * FROM catalog_v WHERE book_id = 1 \gx
```
```text
-[ RECORD 1 ]+----------------------------------------------
book_id      | 1
title        | The Tale of Tsar Saltan
onhand_qty   | 19
display_name | The Tale of Tsar Saltan. Alexander S. Pushkin
authors      | Alexander Sergeyevich Pushkin
```
```sql
=> INSERT INTO operations(book_id, qty_change) VALUES (1,+10);
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM catalog_v WHERE book_id = 1 \gx
```

## Page 22: Page 22 {#source-page-022}

```text
-[ RECORD 1 ]+----------------------------------------------
book_id      | 1
title        | The Tale of Tsar Saltan
onhand_qty   | 29
display_name | The Tale of Tsar Saltan. Alexander S. Pushkin
authors      | Alexander Sergeyevich Pushkin
```
Invalid operations are rejected:
```sql
=> INSERT INTO operations(book_id, qty_change) VALUES (1,-100);
```
```sql
ERROR:  new row for relation "books" violates check constraint "books_onhand_qty_check"
DETAIL:  Failing row contains (1, The Tale of Tsar Saltan, -71).
CONTEXT:  SQL statement "UPDATE books
    SET onhand_qty = onhand_qty + NEW.qty_change
    WHERE book_id = NEW.book_id"
PL/pgSQL function update_onhand_qty() line 3 at SQL statement
```

## Page 23: Practice+ {#source-page-023}

1. Create a trigger that increments the counter (the version field) by 1 each time a row is updated. If a new row is inserted, the corresponding counter must be set to 1. Check the implementation. 2. There are two tables: orders (to store orders) and lines (to keep order lines). Denormalization is required here: automatically update the total cost of the order in the orders table once the corresponding lines have changed.
  - Create the required triggers using transition tables to minimize
  - the number of update operations.

### Speaker notes

2. Use the following commands to create tables:

```sql
CREATE TABLE orders (
```

```text
id int PRIMARY KEY,
total_amount numeric(20,2) NOT NULL DEFAULT 0
);
```

```sql
CREATE TABLE lines (
```

```text
id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
order_id int NOT NULL REFERENCES orders(id),
amount numeric(20,2) NOT NULL
);
```

The orders.total\_amount column must be calculated automatically as the sum of lines.amount values for all rows related to the corresponding order.

## Page 24: 1. Version Counter {#source-page-024}

```sql
=> CREATE DATABASE plpgsql_triggers;
```
```sql
CREATE DATABASE
```
```sql
=> \c plpgsql_triggers
```
```text
You are now connected to database "plpgsql_triggers" as user "student".
```
Table:
```sql
=> CREATE TABLE t(
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    s text,
    version integer
);
```
```sql
CREATE TABLE
```
Trigger function:
```sql
=> CREATE FUNCTION inc_version() RETURNS trigger
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.version := 1;
    ELSE
        NEW.version := OLD.version + 1;
    END IF;
    RETURN NEW;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Trigger:
```sql
=> CREATE TRIGGER t_inc_version
BEFORE INSERT OR UPDATE ON t
FOR EACH ROW EXECUTE FUNCTION inc_version();
```
```sql
CREATE TRIGGER
```
Test it:
```sql
=> INSERT INTO t(s) VALUES ('One');
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM t;
```
```text
 id |  s  | version
----+-----+---------
  1 | One |       1
(1 row)
```
Attempting to set version directly is ignored.
```sql
=> INSERT INTO t(s,version) VALUES ('Two',42);
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM t;
```
```text
 id |  s  | version
----+-----+---------
  1 | One |       1
  2 | Two |       1
(2 rows)
```
Update:
```sql
=> UPDATE t SET s = lower(s) WHERE id = 1;
```
```sql
UPDATE 1
```
```sql
=> SELECT * FROM t;
```

## Page 25: Page 25 {#source-page-025}

```text
 id |  s  | version
----+-----+---------
  2 | Two |       1
  1 | one |       2
(2 rows)
```
This attempt is also ignored.
```sql
=> UPDATE t SET s = lower(s), version = 42 WHERE id = 2;
```
```sql
UPDATE 1
```
```sql
=> SELECT * FROM t;
```
```text
 id |  s  | version
----+-----+---------
  1 | one |       2
  2 | two |       2
(2 rows)
```
### 2. Calculating the Total of Orders Automatically
Create tables with a structure simple enough for the demonstration:
```sql
=> CREATE TABLE orders (
    id integer PRIMARY KEY,
    total_amount numeric(20,2) NOT NULL DEFAULT 0
);
```
```sql
CREATE TABLE
```
```sql
=> CREATE TABLE lines (
   id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   order_id integer NOT NULL REFERENCES orders(id),
   amount numeric(20,2) NOT NULL
);
```
```sql
CREATE TABLE
```
Create a trigger function and a trigger to handle inserts:
```sql
=> CREATE FUNCTION total_amount_ins() RETURNS trigger
AS $$
BEGIN
    WITH l(order_id, total_amount) AS (
        SELECT order_id, sum(amount)
        FROM new_table
        GROUP BY order_id
    )
    UPDATE orders o
    SET total_amount = o.total_amount + l.total_amount
    FROM l
    WHERE o.id = l.order_id;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
The FROM clause in the UPDATE command joins orders to a subquery in a transition table and use the columns of the subquery to calculate the value.
```sql
=> CREATE TRIGGER lines_total_amount_ins
AFTER INSERT ON lines
REFERENCING
    NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION total_amount_ins();
```
```sql
CREATE TRIGGER
```
A function and a trigger for processing updates:

## Page 26: Page 26 {#source-page-026}

```sql
=> CREATE FUNCTION total_amount_upd() RETURNS trigger
AS $$
BEGIN
    WITH l_tmp(order_id, amount) AS (
        SELECT order_id, amount FROM new_table
        UNION ALL
        SELECT order_id, -amount FROM old_table
    ), l(order_id, total_amount) AS (
        SELECT order_id, sum(amount)
        FROM l_tmp
        GROUP BY order_id
        HAVING sum(amount) <> 0
    )
    UPDATE orders o
    SET total_amount = o.total_amount + l.total_amount
    FROM l
    WHERE o.id = l.order_id;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
The HAVING clause skips changes that do not affect the total amount.
```sql
=> CREATE TRIGGER lines_total_amount_upd
AFTER UPDATE ON lines
REFERENCING
    OLD TABLE AS old_table
    NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION total_amount_upd();
```
```sql
CREATE TRIGGER
```
A function and a trigger for handling deletion:
```sql
=> CREATE FUNCTION total_amount_del() RETURNS trigger
AS $$
BEGIN
    WITH l(order_id, total_amount) AS (
        SELECT order_id, -sum(amount)
        FROM old_table
        GROUP BY order_id
    )
    UPDATE orders o
    SET total_amount = o.total_amount + l.total_amount
    FROM l
    WHERE o.id = l.order_id;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> CREATE TRIGGER lines_total_amount_del
AFTER DELETE ON lines
REFERENCING
    OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE FUNCTION total_amount_del();
```
```sql
CREATE TRIGGER
```
Only TRUNCATE is left out, since its trigger cannot use transition tables. However, we know that after a TRUNCATE, the lines table will no longer contain any lines, so the order total can be wiped.
```sql
=> CREATE FUNCTION total_amount_truncate() RETURNS trigger
AS $$
BEGIN
    UPDATE orders SET total_amount = 0;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> CREATE TRIGGER lines_total_amount_truncate
AFTER TRUNCATE ON lines
FOR EACH STATEMENT
EXECUTE FUNCTION total_amount_truncate();
```
```sql
CREATE TRIGGER
```

## Page 27: Page 27 {#source-page-027}

Additionally, total\_amount must not be changed manually, but this cannot be solved with triggers.
Test it.
Add two new orders with no lines:
```sql
=> INSERT INTO orders VALUES (1), (2);
```
```sql
INSERT 0 2
```
```sql
=> SELECT * FROM orders ORDER BY id;
```
```text
 id | total_amount
----+--------------
  1 |         0.00
  2 |         0.00
(2 rows)
```
Add lines to orders:
```sql
=> INSERT INTO lines (order_id, amount) VALUES
    (1,100), (1,100), (2,500), (2,500);
```
```sql
INSERT 0 4
```
```sql
=> SELECT * FROM lines;
```
```text
 id | order_id | amount
----+----------+--------
  1 |        1 | 100.00
  2 |        1 | 100.00
  3 |        2 | 500.00
  4 |        2 | 500.00
(4 rows)
```
```sql
=> SELECT * FROM orders ORDER BY id;
```
```text
 id | total_amount
----+--------------
  1 |       200.00
  2 |      1000.00
(2 rows)
```
Double the amounts in all lines in all orders:
```sql
=> UPDATE lines SET amount = amount * 2;
```
```sql
UPDATE 4
```
```sql
=> SELECT * FROM orders ORDER BY id;
```
```text
 id | total_amount
----+--------------
  1 |       400.00
  2 |      2000.00
(2 rows)
```
Remove the first line of the first order:
```sql
=> DELETE FROM lines WHERE id = 1;
```
```sql
DELETE 1
```
```sql
=> SELECT * FROM orders ORDER BY id;
```
```text
 id | total_amount
----+--------------
  1 |       200.00
  2 |      2000.00
(2 rows)
```
Clear the lines table:
```text
=> TRUNCATE lines;
```
```text
TRUNCATE TABLE
```
```sql
=> SELECT * FROM orders ORDER BY id;
```

## Page 28: Page 28 {#source-page-028}

```text
 id | total_amount
----+--------------
  1 |         0.00
  2 |         0.00
(2 rows)
```
```sql
=> \c postgres
```
```text
You are now connected to database "postgres" as user "student".
```
```sql
=> DROP DATABASE plpgsql_triggers;
```
```sql
DROP DATABASE
```