PostgreSQL Use Cases

Triggers in PostgreSQL are database callback functions that automatically execute when a specified database event occurs, such as an `INSERT`, `UPDATE`, or `DELETE` operation. They are used to enforce business rules, validate input data, and maintain data integrity.

Usage

Triggers automate tasks and enforce rules at the database level, ensuring that specific operations occur in response to changes in the data. They consist of a trigger event, timing, and the associated function to be executed.

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

In this syntax, `CREATE TRIGGER` defines a new trigger, specifying when it should fire (`BEFORE`, `AFTER`, or `INSTEAD OF` an event) and the function it should execute. `BEFORE` triggers are typically used to modify data before it is written to the database, `AFTER` triggers are used for actions that should occur once data is committed, and `INSTEAD OF` triggers are often used on views to perform the required modifications on underlying tables.

• FOR EACH ROW indicates that the trigger function will execute once for each row affected by the triggering event. In contrast, FOR EACH STATEMENT could be used to execute the trigger function once per SQL statement, regardless of the number of rows affected.
• NEW and OLD are special record variables within triggers: NEW contains the new row data for INSERT or UPDATE operations, and OLD contains the existing row data for UPDATE or DELETE operations.

Examples

1. Basic Insert Trigger

CREATE FUNCTION log_insert() RETURNS TRIGGER AS $$
BEGIN
    -- Log every insert operation into log_table
    INSERT INTO log_table(action, timestamp) VALUES ('Insert', now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert
BEFORE INSERT ON main_table
FOR EACH ROW
EXECUTE FUNCTION log_insert();

This example creates a trigger that logs every insert operation on the `main_table` into a `log_table`.

2. Update Trigger with Condition

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
    -- Update the updated_at timestamp field when a row is modified
    NEW.updated_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_time
BEFORE UPDATE ON main_table
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION update_timestamp();

This trigger updates the `updated_at` timestamp field in `main_table` whenever a row is modified.

3. Complex Trigger for Auditing

CREATE FUNCTION audit_changes() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        -- Log DELETE operations into audit_log
        INSERT INTO audit_log(action, old_data, timestamp) VALUES ('DELETE', OLD, now());
    ELSIF (TG_OP = 'UPDATE') THEN
        -- Log UPDATE operations with both old and new data
        INSERT INTO audit_log(action, old_data, new_data, timestamp) VALUES ('UPDATE', OLD, NEW, now());
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON main_table
FOR EACH ROW
EXECUTE FUNCTION audit_changes();

This trigger logs changes made to `main_table` into an `audit_log`, capturing both the old and new data for `UPDATE` operations.

Tips and Best Practices

• Optimize for performance. Triggers can add overhead to database operations, so ensure they are efficient.
• Use triggers sparingly. Avoid overusing triggers to prevent complexity and potential performance issues.
• Ensure atomicity. Make sure that trigger functions are atomic and handle exceptions to maintain data integrity.
• Test thoroughly. Rigorously test triggers in a development environment before deploying them in production to avoid unintended side effects.
• Consider alternatives. Evaluate whether stored procedures or application logic might be better suited for certain operations to ensure clarity and maintainability.