Create Trigger - (PostgreSQL Trigger)

A trigger is a procedure that is automatically executed in response to certain events. In PostgreSQL, triggers can be used to execute code before or after INSERT, UPDATE or DELETE operations on a table. They can be useful for auditing data changes, enforcing business rules, or implementing complex constraints.

In this tutorial, we'll discuss how to create triggers in PostgreSQL.

Syntax

The syntax for creating a trigger in PostgreSQL is as follows:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH {ROW | STATEMENT}
[WHEN (condition)]
EXECUTE FUNCTION function_name();

• trigger_name: The name of the trigger.
• {BEFORE | AFTER}: Indicates whether the trigger should be executed before or after the specified operation.
• {INSERT | UPDATE | DELETE}: Specifies the operation that will trigger the trigger.
• table_name: The name of the table to which the trigger should be applied.
• {ROW | STATEMENT}: Specifies whether the trigger applies to individual rows or to the entire statement.
• condition (Optional): A boolean expression that determines whether the trigger action should be executed.
• function_name: The name of the function to be executed when the trigger is fired.

Example

Let's create a simple trigger that logs any changes to the employees table:

CREATE OR REPLACE FUNCTION log_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO employee_changes (employee_id, operation, timestamp)
    VALUES (OLD.id, 'DELETED', NOW());
  ELSE
    INSERT INTO employee_changes (employee_id, operation, timestamp)
    VALUES (NEW.id, 'UPDATED', NOW());
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employee_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_changes();

In this example, we create a function log_employee_changes() that inserts a record into a table called employee_changes whenever an employee is inserted, updated or deleted. We then create a trigger called employee_changes that executes this function for every row affected.

Explanation

The log_employee_changes() function is defined with RETURNS TRIGGER to indicate that it can be used as a trigger function. Within the function, we check the TG_OP (Trigger Operation) variable to determine whether the operation was a DELETE or an INSERT/UPDATE. We then insert a record into the employee_changes table with the appropriate values.

The employee_changes trigger is defined with AFTER INSERT OR UPDATE OR DELETE to indicate that it should be executed after any of those operations. We use the FOR EACH ROW clause to indicate that the trigger should be executed for each row affected by the operation. We then use the EXECUTE FUNCTION clause to specify the function to be executed.

Use

Triggers can be useful for enforcing business rules, auditing data changes, or implementing complex constraints that cannot be handled by constraints alone.

Important Points

• Triggers are executed automatically in response to certain events.
• Triggers can be defined as BEFORE or AFTER a specified operation.
• Triggers can be defined for each row or for the entire statement.
• The TG_OP variable can be used to determine the type of operation that triggered the trigger.

Summary

In this tutorial, we showed you how to create triggers in PostgreSQL using the CREATE TRIGGER statement. We discussed the syntax, example, output, explanation, use, and important points of creating triggers in PostgreSQL. With this knowledge, you can now create triggers that can be used to enforce business rules, audit data changes, or implement complex constraints.