PostgreSQL Introduction to Triggers

Triggers in PostgreSQL are special procedures that are automatically executed or triggered when certain events occur in a database table. They are used to maintain the integrity of the data, enforce business rules, and automate complex checks or transformations.

Usage

Triggers are employed to automatically respond to events such as `INSERT`, `UPDATE`, or `DELETE` operations on a table. They can be set to execute `BEFORE`, `AFTER`, or `INSTEAD OF` the event and can optionally be conditioned on whether the event succeeds. Triggers can be defined at the row level, executing for each affected row, or at the statement level, executing once per SQL statement.

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE | DELETE }
ON table_name
FOR EACH { ROW | STATEMENT }
EXECUTE FUNCTION function_name();

In this syntax, `CREATE TRIGGER` defines a new trigger named `trigger_name` that calls a specified function `function_name` either before, after, or instead of a data-modifying event on `table_name`.

Triggers interact with transaction control commands like `COMMIT` and `ROLLBACK`. If a trigger causes an error, the entire transaction will typically be rolled back unless explicitly handled.

Examples

1. Basic Trigger for Auditing

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    operation VARCHAR(10),
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_insert() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log(operation) VALUES ('INSERT');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_audit
AFTER INSERT ON target_table
FOR EACH ROW
EXECUTE FUNCTION log_insert();

This basic example logs every `INSERT` operation on `target_table` into the `audit_log` table.

2. Trigger with Conditional Logic

CREATE OR REPLACE FUNCTION check_salary() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < OLD.salary THEN
        RAISE EXCEPTION 'Salary cannot be decreased!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER salary_check
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary();

This trigger ensures that an employee's salary cannot be decreased during an update operation.

3. Cascade Trigger

CREATE OR REPLACE FUNCTION delete_cascade() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM orders WHERE customer_id = OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cascade_delete
AFTER DELETE ON customers
FOR EACH ROW
EXECUTE FUNCTION delete_cascade();

This example automatically deletes all orders associated with a customer when the customer is removed.

Managing Triggers

To disable or drop a trigger when it's no longer needed, you can use the following commands:

ALTER TABLE table_name DISABLE TRIGGER trigger_name;
DROP TRIGGER trigger_name ON table_name;

To view existing triggers in a database, you can query the `pg_trigger` system catalog.

Tips and Best Practices

• Keep triggers simple. Complex logic can lead to performance bottlenecks and maintenance challenges.
• Test thoroughly. Ensure your triggers work as expected by testing them under various scenarios.
• Document triggers. Clearly document the purpose and operation of each trigger to aid future developers.
• Consider permissions. Ensure that users triggering the events have appropriate permissions to execute the associated functions.
• Monitor performance impact. Be aware that extensive use of triggers may affect performance, especially on high-transaction tables.
• Avoid recursive triggers. Recursive triggers can lead to infinite loops and should be handled with caution.