PostgreSQL CREATE TRIGGER

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

Usage

Triggers are used to automate tasks that should occur when specific database events happen, reducing the need for manual intervention or application-side logic. They are defined using the `CREATE TRIGGER` statement, specifying the event and the action to be taken.

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

In this syntax, `CREATE TRIGGER` defines a new trigger named `trigger_name` that executes a specified function (`function_name`) either before, after, or instead of the specified event on the `table_name`. The `FOR EACH ROW` clause indicates the trigger executes once for each row affected by the event, while `FOR EACH STATEMENT` executes once per triggering statement, regardless of how many rows are affected.

Examples

1. Basic Insert Trigger

sql
CREATE FUNCTION log_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation) VALUES ('employees', 'INSERT');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_employee_inserts
AFTER INSERT ON employees
FOR EACH ROW EXECUTE PROCEDURE log_insert();

This example creates a trigger that logs every insert operation on the `employees` table into an `audit_log` table.

2. Update Trigger with Condition

sql
CREATE FUNCTION check_salary_update() 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 prevent_salary_decrease
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE check_salary_update();

This trigger prevents any decrease in the salary of employees, ensuring that salary updates can only increase or remain the same. Here, `NEW` and `OLD` represent the new and old states of the row being operated on, respectively.

3. Trigger for Cascade Delete

sql
CREATE FUNCTION delete_related_records() RETURNS TRIGGER AS $$
BEGIN
  DELETE FROM orders WHERE customer_id = OLD.customer_id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cascade_delete_orders
AFTER DELETE ON customers
FOR EACH ROW EXECUTE PROCEDURE delete_related_records();

This example sets up a trigger to automatically delete all orders associated with a customer when that customer is deleted.

4. INSTEAD OF Trigger Example

INSTEAD OF triggers are typically used with views to perform actions instead of the default operation.

sql
CREATE FUNCTION log_view_update() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation) VALUES ('view_name', 'UPDATE');
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_view_updates
INSTEAD OF UPDATE ON my_view
FOR EACH ROW EXECUTE PROCEDURE log_view_update();

This trigger logs attempts to update a view instead of performing the update.

Tips and Best Practices

• Clearly define trigger conditions. Ensure the conditions under which triggers fire are well-defined to avoid unexpected behavior.
• Use triggers sparingly. Overuse of triggers can lead to complex interdependencies and make debugging difficult.
• Document triggers thoroughly. Provide clear documentation on what each trigger does and why it is used for future reference.
• Test thoroughly. Always test triggers in a development environment to ensure they function as intended without performance degradation.
• Consider performance implications. Complex triggers can impact performance; monitor and optimize them where necessary.
• Prevent recursive invocation. Use commands like `ALTER TABLE ... ENABLE TRIGGER ...` to control trigger activation and prevent potential recursive invocation loops.