PostgreSQL - Triggers

PostgreSQL - Triggers

A trigger is a special type of stored function that is automatically executed when a specific database event (like INSERT, UPDATE, DELETE, or TRUNCATE) occurs on a table or view.

Key Concepts of Triggers

• Event Timing: Triggers can be configured to fire BEFORE, AFTER, or INSTEAD OF the specified event.
• Execution Level: A trigger can fire FOR EACH ROW affected by the operation, or FOR EACH STATEMENT, which executes only once per command regardless of the number of rows modified.
• Trigger Function: The logic of the trigger is defined in a separate function, typically written in PL/pgSQL, which must return a value of type trigger.
• Special Variables: Inside a row-level trigger function, you can access the old and new data of the row using the OLD and NEW records, respectively (NEW for INSERT/UPDATE, OLD for UPDATE/DELETE).

Syntax

Creating a trigger is a two-step process: first, define the trigger function, and second, bind it to a table.

1. Define the Trigger Function

CREATE OR REPLACE FUNCTION function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger logic goes here
    RETURN NEW; -- or OLD, or NULL depending on the logic
END;
$$ LANGUAGE plpgsql;

2. Create the Trigger

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FOR EACH { ROW | STATEMENT } ]
EXECUTE FUNCTION function_name();

Example: Auditing Table Changes

A classic use case for triggers is creating an audit trail. Let’s create a trigger that logs every salary update for an employees table.

Step 1: Set up the Tables

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    salary NUMERIC(10, 2) NOT NULL
);

CREATE TABLE salary_audits (
    audit_id SERIAL PRIMARY KEY,
    employee_id INT NOT NULL,
    old_salary NUMERIC(10, 2),
    new_salary NUMERIC(10, 2),
    changed_by TEXT DEFAULT current_user,
    changed_at TIMESTAMPTZ DEFAULT now()
);

Step 2: Create the Trigger Function

This function will insert a record into salary_audits if the salary has changed.

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the UPDATE operation actually changed the salary
    -- TG_OP is a special variable that holds the operation type ('UPDATE', 'INSERT', etc.)
    IF TG_OP = 'UPDATE' AND NEW.salary <> OLD.salary THEN
        INSERT INTO salary_audits(employee_id, old_salary, new_salary)
        VALUES(OLD.id, OLD.salary, NEW.salary);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 3: Create the Trigger

Now, we bind this function to the employees table to fire after an update.

CREATE TRIGGER employees_salary_update_trigger
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

Step 4: Test the Trigger

-- First, insert a sample employee
INSERT INTO employees (name, salary) VALUES ('Alice', 60000.00);

-- Now, update the salary
UPDATE employees SET salary = 65000.00 WHERE name = 'Alice';

-- Check the audit log
SELECT * FROM salary_audits;

The select query will show a new row in salary_audits documenting the change from 60000.00 to 65000.00.

Listing and Dropping Triggers

In psql, you can see triggers on a table with the \d table_name command. To drop a trigger, use the DROP TRIGGER command.

DROP TRIGGER trigger_name ON table_name;

Potential Pitfalls

• Performance Impact: Triggers add overhead to DML operations. Complex trigger logic can significantly slow down INSERT, UPDATE, and DELETE statements.
• Debugging: Hidden logic in triggers can make debugging application behavior difficult. It’s not always obvious that a trigger is modifying data.
• Cascading Triggers: Be careful of creating triggers that cause other triggers to fire, which can lead to complex and hard-to-predict behavior.