Triggers

About

A trigger is an action or event that causes another event to occur. So a triggering SQL statement causes another SQL statement (the trigger statement) to be executed.

Triggers can be used to:

• Keep a database consistent;

• Auditing events;

• Logging;

• Enforcing complex constraints;

• Replications and more.

A PostgreSQL trigger is a function invoked automatically whenever 'an event' associated with a table occurs - like (Insert, Update, Delete or Truncate)

A trigger can be associated with a specific Table, View or Foreign Table.

Triggers can trigger:

• BEFORE: If the trigger is invoked before an event, it can skip the operation for the current row or even change the row being updated or inserted.

• AFTER: All changes are available to the trigger.

• INSTEAD: Of the events/operation.

When multiple triggers are set in a table the order of execution is:

1. BEFORE triggers, in alphabetical order.

2. AFTER trigger, also in alphabetical order.

You can call Stored Procedures and Functions inside triggers.

Avoid:

• Too many nested triggers can make it very hard to debug and troubleshoot.

• Aggregating/summarizing over the table that you are updating.

• Reading data from a table that is updating during the same transaction.

Types of triggers

Row level triggers

If the trigger is marked for each row, then the trigger function will be called for each row that is getting modified by the event.

Statement level triggers

The FOR EACH STATEMENT option will call the trigger function only once for each statement, regardless of the number of the rows getting modified.

Limitations

When	Event	Row-level only for	Statement-level only for
BEFORE	INSERT / UPDATE / DELETE	Tables	Tables and Views
	TRUNCATE	❌	Tables
AFTER	INSERT / UPDATE / DELETE	Tables	Tables and Views
	TRUNCATE	❌	Tables
INSTEAD OF	INSERT / UPDATE / DELETE	Views	❌
	TRUNCATE	❌	❌

Creating

1. First, create a trigger function.

2. Then, bind the trigger function to a table.

Trigger function

You can sufix with trigger on function that are trigger functions.

CREATE FUNCTION fn_function_name_trigger()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
    BEGIN
        -- trigger logic
    END;
$$

Bind the trigger function

You can use trg prefix on trigger names when creating them.

CREATE TRIGGER trg_trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE | TRUNCATE }
ON table_name
    [ FOR [EACH] { ROW | STATEMENT } ] EXECUTE PROCEDURE fn_function_name_trigger();

Deleting

DROP TRIGGER trg_trigger_name;

Special variables

Triggers have special OLD and NEW variables to access values, which:

• OLD represents the values before the event.

• NEW represents the values after.

Depending on the event they can be NULL.

CREATE OR REPLACE FUNCTION fn_table_audit_trigger()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
    BEGIN
        IF NEW.name <> OLD.name THEN
            INSERT INTO audits (name) VALUES (OLD.name);
        END IF;
        -- To return the data to be updated, otherwise the data will not be updated
        RETURN NEW;
    END;
$$

CREATE TRIGGER trg_audits
    BEFORE UPDATE
ON table_name
    FOR EACH ROW EXECUTE PROCEDURE fn_table_audit_trigger();

Usage

Using triggers for auditing

CREATE OR REPLACE FUNCTION fn_table_audit_trigger()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
    DECLARE
        old_row json = NULL;
        new_row json = NULL;
    BEGIN
        -- TG_OP is a special trigger variable
        -- If the current event is UPDATE or DELETE
        IF TG_OP IN ('UPDATE', 'DELETE') THEN
            old_row = row_to_json(OLD);
        END IF;
        
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            new_data = row_to_json(NEW);
        END IF;
        
        INSERT INTO audits (
            username,
            created_at,
            table_name,
            operaion,
            row_before,
            row_after
        ) VALUES (
            -- This will be the connected user running the query
            SESSION_USER,
            CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
            TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
            TG_OP,
            old_row,
            new_row
        );
    
        RETURN NEW;
    END;
$$

CREATE TRIGGER trg_audits
    AFTER INSERT OR UPDATE OR DELETE
ON table_name
    FOR EACH ROW EXECUTE PROCEDURE fn_table_audit_trigger();