7.5 Triggers

Triggers (PostgreSQL edition)

Section outline

Introduction to triggers as event-condition-action (ECA) rules.
Explanation of the three ways triggers differ from constraints: they are awakened by specific events, they test a condition, and they perform an action if the condition is met.
Types of triggering events: insert, delete, update, or transaction end.
The role of the condition: if it does not hold, nothing happens.
The nature of the action: it can modify the effects of the event, abort the transaction, or perform any other database operations.

#1

read: Section 7.5: Triggers (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Introduction to triggers as event-condition-action (ECA) rules.
- Explanation of the three ways triggers differ from constraints: they are awakened by specific events, they test a condition, and they perform an action if the condition is met.
- Types of triggering events: insert, delete, update, or transaction end.
- The role of the condition: if it does not hold, nothing happens.
- The nature of the action: it can modify the effects of the event, abort the transaction, or perform any other database operations.

PL/pgSQL

PL/pgSQL is PostgreSQL’s built-in procedural language. It sits on top of SQL and adds variables, control flow, exceptions, loops, and triggers. You use it when plain SQL is insufficient or when logic needs to run inside the database.

What is PL/pgSQL:

A procedural extension of SQL, compiled and stored by PostgreSQL.
Executes inside the server process (no network round-trips).
Common uses: trigger functions, custom validations, data transformations, stored logic, batch jobs.

Basic Structure of a Function

CREATE OR REPLACE FUNCTION <function_name> (<parameter> <type>, ...)
RETURNS <return-type>
LANGUAGE plpgsql
AS $$
DECLARE
    <local_varaibles>;
    ...
BEGIN
    <SQL_statements>
    ...
    RETURN <...>;
END;
$$;

Variables and Types

Types are any PostgreSQL type. Assignment uses :=.

DECLARE
    counter int := 0;
    username text;
    calculated numeric(10,2);

result := x + y;

SQL in the function

Simple SQL

SELECT count(*) INTO counter
FROM users;

Modifying data

UPDATE accounts
SET balance = balance - amount
WHERE id = account_id;

Returning sets

RETURN QUERY SELECT * FROM users WHERE active;

This requires the function to declare the return type as:

RETURNS SETOF <row_type>

Control Flow

IF-ELSIF-ELSE-END IF

IF amount < 0 THEN
    RAISE EXCEPTION 'amount must be non-negative';
ELSIF amount = 0 THEN
    RAISE NOTICE 'no-op';
ELSE
    RAISE NOTICE 'OK';
END IF;

LOOP-END LOOP

LOOP
    counter := counter + 1;
    EXIT WHEN counter > 10;
END LOOP;

FOR LOOP-END LOOP

Iteration over numeric range.

FOR i IN 1..5 LOOP
    RAISE NOTICE 'i=%', i;
END LOOP;

Iteration over query result.

FOR rec IN SELECT id, name FROM users LOOP
    RAISE NOTICE 'User: %, %', rec.id, rec.name;
END LOOP;

Exception Handling

BEGIN
    SELECT 1 / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught error';
END;

Raise statements

They are debugging statements

RAISE NOTICE 'x=%', x;
RAISE WARNING 'threshold reached';
RAISE EXCEPTION 'Fatal: %', msg;

Triggers Overview

Triggers are defined using the CREATE TRIGGER statement. It instructs the database engine to execute trigger functions based on certain events.

Core Structure of CREATE TRIGGER

The syntax skeleton:

CREATE TRIGGER <trigger_name>
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE | TRUNCATE }
ON <table_or_view>
[ FOR EACH ROW | FOR EACH STATEMENT ]
[ WHEN ( condition ) ]
EXECUTE FUNCTION <function_name>();

Every trigger definition answers three questions:

When does it fire? → BEFORE or AFTER
What does it fire for? → INSERT, UPDATE, DELETE, TRUNCATE
How often does it fire? → per-row or per-statement

BEFORE triggers

CREATE TRIGGER t
BEFORE INSERT OR UPDATE ON mytable
FOR EACH ROW
EXECUTE FUNCTION fn();

Characteristics

Fired before the database modifies the row.
Only meaningful as row-level (though you can attach statement-level; it just can’t modify the row).
Can:
- modify NEW
- reject the operation (exception)
- skip the row (RETURN NULL)
- Access to OLD and/or NEW depending on operation type.

Typical use

Validation, normalization, enforcing rules, computed columns.

AFTER triggers

CREATE TRIGGER t
AFTER UPDATE ON mytable
FOR EACH ROW
EXECUTE FUNCTION fn();

Characteristics

Fired after the row is successfully changed.
Row-level AFTER triggers can inspect OLD/NEW, but cannot modify the outcome.
Statement-level AFTER triggers fire once per statement.
Return value is ignored.

Typical use

Auditing, logging, maintaining summary tables, NOTIFY events.

INSTEAD OF triggers

CREATE TRIGGER t
INSTEAD OF INSERT ON myview
FOR EACH ROW
EXECUTE FUNCTION fn();

Only for views.
Completely replace default behavior.
Used to make views writable.

ROW-level triggers

CREATE TRIGGER <trigger>
...
FOR EACH ROW
EXECUTE FUNCTION <function>;

Characteristics

Fires once per affected row.
Access to:
- NEW (INSERT/UPDATE)
- OLD (UPDATE/DELETE)
Only kind that can:
- modify NEW
- cancel specific rows

Example

CREATE TRIGGER validate_price
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION validate_price_fn();

STATEMENT-level triggers

CREATE TRIGGER <trigger>
...
FOR EACH STATEMENT
EXECUTE FUNCTION <function>;

Characteristics

Fires once per SQL statement, regardless of number of rows.
Has no access to OLD or NEW.
Cannot influence row data.
Used for global logging or high-level enforcement.

Example

CREATE TRIGGER log_updates
AFTER UPDATE ON users
FOR EACH STATEMENT
EXECUTE FUNCTION log_change_fn();

Trigger functions

Trigger functions are the same language but

RETURNS trigger
It has access to special variables:

CREATE FUNCTION example_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Operation=%', TG_OP;
    RAISE NOTICE 'New row=%', row_to_json(NEW);

    RETURN NEW; -- or OLD based on trigger type
END;
$$;

NEW and OLD are pseudo-records for row-before/after.

`OLD` and `NEW`

INSERT

OLD: not available (null reference)
NEW: row to be inserted (before insertion)

UPDATE

OLD: the row before the update
NEW: the row after the update

DELETE

OLD: the row to be deleted
NEW: not available (null reference)

Examples

Enforcing a constraint

CREATE OR REPLACE FUNCTION check_age()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.age < 18 THEN
        RAISE EXCEPTION 'age must be >= 18';
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER users_age_check
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION check_age();

Compute a Derived Value

CREATE OR REPLACE FUNCTION compute_total()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.total := NEW.qty * NEW.unit_price;
    RETURN NEW;
END;
$$;

More special variables

TG_OP

Operation type as an uppercase text string.

Possible values:
1. ‘INSERT’
2. ‘UPDATE’
3. ‘DELETE’
Useful when a single trigger function is attached to multiple events:
```
IF TG_OP = 'INSERT' THEN
    ...
ELSIF TG_OP = 'UPDATE' THEN
    ...
END IF;
```
TG_TABLE_NAME

The unqualified table name the trigger is firing on.
TG_TABLE_SCHEMA

The schema name
TG_WHEN

Text ‘BEFORE’ or ‘AFTER’. Not often used because trigger timing is decided in the trigger definition.
TG_LEVEL

Either ‘ROW’ or ‘STATEMENT’.

Defining Triggers

CREATE TRIGGER <trigger_name>
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE | TRUNCATE }
ON <table_or_view>
[ FOR EACH ROW | FOR EACH STATEMENT ]
[ WHEN ( condition ) ]
EXECUTE FUNCTION <function_name>();

BEFORE triggers

Executed before PostgreSQL performs the actual INSERT/UPDATE/DELETE on the row.

Key abilities

Modify NEW (e.g., enforce or compute values)
Validate and reject changes (raise exception)
Cancel a row by returning NULL
Preprocess data before it gets written
Prevent an operation entirely

Common uses

Auto-updating timestamps
Enforcing custom constraints
Sanitizing data
Auto-filling computed columns

Behavior rules

For INSERT/UPDATE: return NEW to continue
For DELETE: return OLD
Returning NULL skips the row

Example of BEFORE trigger

CREATE FUNCTION normalize_email()
RETURNS trigger AS $$
BEGIN
    NEW.email := lower(NEW.email);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER normalize_email_tg
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION normalize_email();

AFTER triggers

Executed after the row has been modified and the data is already written.

Key abilities

Observe changes but cannot modify them
Perform side-effects that depend on final committed data
Write audit logs, update summary tables, notify external systems

Common uses

Audit tables
Maintaining denormalized reporting tables
Sending NOTIFY events
Enforcing cross-table logic that must see final results

Behavior rules

Return value is ignored; must return something but it doesn’t matter
No ability to cancel the insert/update/delete

Example of AFTER trigger

CREATE FUNCTION audit_user_changes()
RETURNS trigger AS $$
BEGIN
    INSERT INTO user_audit(data, changed_at)
    VALUES (row_to_json(NEW), now());
    RETURN NEW;  -- ignored
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_users
AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION audit_user_changes();

INSTEAD OF triggers

These only apply to views, not tables. They intercept operations aimed at a view and let you define custom behavior.

What they do

The underlying table(s) are not automatically modified
Your trigger decides what to do with the incoming NEW/OLD row
Typically used to make a view writable when PostgreSQL wouldn’t allow it

Common uses

Making a complex view appear updatable
Routing an INSERT on a view into several tables
Enforcing special rules on virtualized tables

Behavior rules

RETURN NEW means “the operation succeeded for the view”
No automatic change occurs unless your code executes SQL
Work on INSERT, UPDATE, and DELETE on views

Example of INSTEAD OF trigger

CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true;

CREATE FUNCTION active_users_insert()
RETURNS trigger AS $$
BEGIN
    -- Insert into real table instead
    INSERT INTO users(id, email, active)
    VALUES (NEW.id, NEW.email, true);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER active_users_ins
INSTEAD OF INSERT ON active_users
FOR EACH ROW EXECUTE FUNCTION active_users_insert();

Triggers (PostgreSQL edition)

PL/pgSQL

What is PL/pgSQL:

Basic Structure of a Function

Variables and Types

SQL in the function

Control Flow

Exception Handling

Raise statements

Triggers Overview

Core Structure of CREATE TRIGGER

BEFORE triggers

Characteristics

AFTER triggers

Characteristics

INSTEAD OF triggers

ROW-level triggers

Characteristics

Example

STATEMENT-level triggers

Characteristics

Trigger functions

OLD and NEW

Examples

More special variables

Defining Triggers

BEFORE triggers

Example of BEFORE trigger

AFTER triggers

Example of AFTER trigger

INSTEAD OF triggers

Example of INSTEAD OF trigger

`OLD` and `NEW`