PostgreSQL Statement-level Triggers

Triggers in PostgreSQL are special procedures that are automatically executed or fired when certain events occur in a database table. Statement-level triggers are executed once per SQL statement, regardless of the number of rows affected by the statement.

Usage
Statement-level triggers are used to enforce business rules, validate input, or maintain audit logs when a statement affects any number of rows. They are defined to act before or after `INSERT`, `UPDATE`, or `DELETE` operations on a table.

sql
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH STATEMENT
EXECUTE PROCEDURE function_name();

In this syntax, `FOR EACH STATEMENT` specifies that the trigger should fire once per statement, and `EXECUTE PROCEDURE` defines the function to be called. Note that statement-level triggers do not have access to the `OLD` and `NEW` row values, as they are not row-specific.

Examples

1. Basic Statement-level Trigger

sql
-- This function logs an entry into audit_log whenever an INSERT, UPDATE, or DELETE operation is performed on the employees table.
CREATE FUNCTION log_total_changes() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log(event_type, event_time)
  VALUES (TG_OP, now());
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_changes
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH STATEMENT
EXECUTE PROCEDURE log_total_changes();

2. Trigger for Insert Operations

sql
-- This function logs every INSERT operation on the employees table into the insert_log table with the current timestamp.
CREATE FUNCTION log_inserts() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO insert_log(table_name, insert_time)
  VALUES (TG_TABLE_NAME, now());
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_employee_inserts
AFTER INSERT
ON employees
FOR EACH STATEMENT
EXECUTE PROCEDURE log_inserts();

3. Conditional Trigger Execution

sql
-- This function conditionally calls a notification function only when a DELETE operation is performed on the orders table.
CREATE FUNCTION notify_admin() RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
    PERFORM notify_admin_function();
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER admin_notification
AFTER DELETE
ON orders
FOR EACH STATEMENT
EXECUTE PROCEDURE notify_admin();

In these examples, `TG_OP` refers to the type of operation (`INSERT`, `UPDATE`, or `DELETE`), and `TG_TABLE_NAME` refers to the name of the table on which the trigger is defined.

Tips and Best Practices

• Limit trigger complexity. Keep trigger logic simple to avoid unintended side effects and maintain performance.
• Use triggers sparingly. Excessive use can lead to maintenance challenges and make debugging difficult.
• Clearly document triggers. Always document the purpose and function of triggers within your database.
• Test thoroughly. Ensure triggers are thoroughly tested to validate their behavior under various scenarios.
• Consider performance impact. Be aware that triggers can affect performance, especially in high-transaction environments.
• Consider using row-level triggers for row-specific logic. Statement-level triggers are not suitable for row-specific operations, so use row-level triggers as needed.
• Understand trigger differences. Statement-level triggers execute once per statement without row-specific access, while row-level triggers execute for each affected row and can access `OLD` and `NEW` row values.