PostgreSQL – Triggers

Triggers in PostgreSQL

In PostgreSQL, a trigger is a database object that automatically executes a specified function or procedure in response to certain events on a table or view. Triggers are useful for enforcing business rules, auditing changes, or maintaining derived values in other tables. Events that can activate triggers include INSERT, UPDATE, and DELETE operations.

This tutorial explains how to create and use triggers in PostgreSQL, with practical examples and detailed steps for implementation.

---

How Triggers Work

A trigger in PostgreSQL consists of two main components:

• Trigger Function: A user-defined function written in PL/pgSQL or another supported language that defines the logic to be executed when the trigger is fired.
• Trigger Definition: Links the trigger function to a table and specifies the event(s) (e.g., INSERT, UPDATE, or DELETE) and timing (BEFORE or AFTER) when the trigger should fire.

Trigger Types:

• BEFORE: Executes the trigger function before the triggering event (e.g., before an INSERT or UPDATE operation).
• AFTER: Executes the trigger function after the triggering event.
• INSTEAD OF: Used with views to execute the trigger function instead of the triggering event.

---

Syntax for Creating a Trigger

CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger logic here
    RETURN NEW; -- For BEFORE triggers
    RETURN NULL; -- For AFTER triggers
END;
$$ LANGUAGE plpgsql;

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

Let’s explore practical examples of creating and using triggers in PostgreSQL.

---

Example 1: Auditing Changes with a Trigger

In this example, we’ll create two tables:

• employees: Stores employee data.
• audit_log: Logs any changes made to the employees table.

Create the tables:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100)
);

CREATE TABLE audit_log (
    log_id SERIAL PRIMARY KEY,
    operation_type VARCHAR(10),
    employee_id INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create a trigger function to log changes:

CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (operation_type, employee_id)
    VALUES (TG_OP, NEW.employee_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Create a trigger to call the function on INSERT operations:

CREATE TRIGGER after_insert_trigger
AFTER INSERT
ON employees
FOR EACH ROW
EXECUTE FUNCTION log_changes();

Insert data into the employees table:

INSERT INTO employees (employee_name, department)
VALUES ('Alice', 'HR'), ('Bob', 'IT');

Query the audit_log table to see the logged changes:

SELECT * FROM audit_log;

Explanation: The trigger function captures the operation type (INSERT) and the employee ID, logging them into the audit_log table. This is useful for tracking changes to sensitive data.

---

Example 2: Validating Data with a Trigger

You can use triggers to enforce custom data validation rules. For example, ensure that employee names are not left empty when inserting or updating records.

Create a validation trigger function:

CREATE OR REPLACE FUNCTION validate_employee_name()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.employee_name IS NULL OR NEW.employee_name = '' THEN
        RAISE EXCEPTION 'Employee name cannot be empty';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Create a trigger for the validation:

CREATE TRIGGER before_insert_update_trigger
BEFORE INSERT OR UPDATE
ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_employee_name();

Try inserting a record with an empty name:

INSERT INTO employees (employee_name, department)
VALUES ('', 'Finance');

Result: PostgreSQL will throw an error due to the validation logic in the trigger function:

Explanation: The trigger function enforces data integrity by raising an exception if the employee name is empty, ensuring the quality of data in the employees table.

---

Conclusion

Triggers in PostgreSQL are a powerful tool for automating database logic, enforcing business rules, and maintaining data integrity. By using trigger functions, you can perform actions like auditing changes, validating data, and maintaining consistency across related tables.