Triggers are a powerful feature in PostgreSQL that allow you to automatically execute a specified function in response to certain events on a table or view. These events can be INSERT, UPDATE, or DELETE operations. Triggers can be used to enforce business rules, maintain audit trails, and synchronize tables, among other tasks.

• Trigger: A database object that is automatically executed or fired when certain events occur.
• Trigger Function: A function that is called by the trigger. This function contains the logic that you want to execute when the trigger fires.
• Event: The operation that causes the trigger to fire (INSERT, UPDATE, DELETE).
• Timing: Specifies when the trigger should fire (BEFORE or AFTER the event).

1. Row-Level Triggers: Fired for each row affected by the event.
2. Statement-Level Triggers: Fired once for the entire statement, regardless of how many rows are affected.

1. Create a Trigger Function: This function contains the logic to be executed when the trigger fires.
2. Create the Trigger: Define the event and timing for the trigger, and associate it with the trigger function.

Let's create a simple example where we log changes to a table called employees into a employees_audit table.

Step 1: Create the Audit Table

Step 2: Create the Trigger Function

Step 3: Create the Trigger

• Audit Table: employees_audit stores the changes made to the employees table.
• Trigger Function: log_employee_changes checks the type of operation (UPDATE or DELETE) and inserts a record into the employees_audit table.
• Trigger: employee_changes is set to fire AFTER an UPDATE or DELETE operation on the employees table, for each affected row.

Task: Create a trigger that logs INSERT operations on the employees table into the employees_audit table.

Solution:

1. Modify the Trigger Function:

   CREATE OR REPLACE FUNCTION log_employee_changes() RETURNS TRIGGER AS $$
   BEGIN
       IF TG_OP = 'INSERT' THEN
           INSERT INTO employees_audit (employee_id, operation, new_name)
           VALUES (NEW.id, 'I', NEW.name);
       ELSIF TG_OP = 'UPDATE' THEN
           INSERT INTO employees_audit (employee_id, operation, old_name, new_name)
           VALUES (OLD.id, 'U', OLD.name, NEW.name);
       ELSIF TG_OP = 'DELETE' THEN
           INSERT INTO employees_audit (employee_id, operation, old_name)
           VALUES (OLD.id, 'D', OLD.name);
       END IF;
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   

2. Create the Trigger:

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

Task: Create a statement-level trigger that logs the number of rows affected by UPDATE operations on the employees table.

Solution:

1. Create the Log Table:

   CREATE TABLE update_log (
       log_id SERIAL PRIMARY KEY,
       table_name VARCHAR(100),
       operation VARCHAR(10),
       row_count INT,
       log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

2. Create the Trigger Function:

   CREATE OR REPLACE FUNCTION log_update_count() RETURNS TRIGGER AS $$
   BEGIN
       INSERT INTO update_log (table_name, operation, row_count)
       VALUES (TG_TABLE_NAME, TG_OP, (SELECT COUNT(*) FROM employees WHERE id = NEW.id));
       RETURN NULL;
   END;
   $$ LANGUAGE plpgsql;
   

3. Create the Trigger:

   CREATE TRIGGER update_count_log
   AFTER UPDATE ON employees
   FOR EACH STATEMENT
   EXECUTE FUNCTION log_update_count();
   

• Returning the Correct Value: Ensure that your trigger function returns the correct value (NEW for INSERT and UPDATE, OLD for DELETE).
• Trigger Timing: Be clear about when you want the trigger to fire (BEFORE or AFTER the event).
• Performance Considerations: Triggers can impact performance, especially if they contain complex logic or are fired frequently.

In this section, you learned about PostgreSQL triggers, including their types, how to create them, and practical examples. Triggers are a powerful tool for automating tasks and enforcing business rules within your database. By understanding and utilizing triggers, you can enhance the functionality and integrity of your PostgreSQL databases.

Next, we will explore Stored Procedures and Functions in PostgreSQL, which allow you to encapsulate and reuse complex logic within your database.