Database triggers are a powerful feature in PostgreSQL that allow you to automatically execute a specified function in response to certain events on a particular table or view. They are useful for enforcing business rules, maintaining audit trails, or performing automatic updates. A trigger is associated with a table and can be set to fire either before or after specific actions such as INSERT, UPDATE, or DELETE.

Here's a step-by-step guide to understanding and using triggers in PostgreSQL, with examples related to banking, customers, accounts, and transactions.

1. Creating a Trigger Function

   Before you can create a trigger, you need to define a trigger function. This function will contain the logic that you want to execute when the trigger fires.

   CREATE OR REPLACE FUNCTION log_transaction()
   RETURNS TRIGGER AS $$
   BEGIN
       INSERT INTO transaction_audit (account_id, action, action_time)
       VALUES (NEW.account_id, TG_OP, NOW());
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
         

   • log_transaction() is the name of the function.
   • NEW refers to the new row being inserted or updated.
   • TG_OP is a special variable that holds the type of operation (INSERT, UPDATE, DELETE).
2. Creating a Trigger

   Once the function is created, you can create a trigger that will call this function in response to specific events.

   CREATE TRIGGER transaction_trigger
   AFTER INSERT OR UPDATE ON transactions
   FOR EACH ROW
   EXECUTE FUNCTION log_transaction();
         

   • transaction_trigger is the name of the trigger.
   • AFTER INSERT OR UPDATE specifies that the trigger will fire after an INSERT or UPDATE operation.
   • FOR EACH ROW indicates that the trigger will fire once for each row affected by the operation.
3. Example Use Case: Auditing Transactions

   Suppose you want to keep track of all transactions that occur in the transactions table. You can use the above trigger to automatically insert a record into the transaction_audit table every time a transaction is inserted or updated.

   • Table Definitions:

     CREATE TABLE transactions (
         transaction_id SERIAL PRIMARY KEY,
         account_id INT NOT NULL,
         amount DECIMAL(10, 2) NOT NULL,
         transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
     );
     
     CREATE TABLE transaction_audit (
         audit_id SERIAL PRIMARY KEY,
         account_id INT NOT NULL,
         action VARCHAR(10) NOT NULL,
         action_time TIMESTAMP NOT NULL
     );
             

   • Trigger Function:

     CREATE OR REPLACE FUNCTION log_transaction()
     RETURNS TRIGGER AS $$
     BEGIN
         INSERT INTO transaction_audit (account_id, action, action_time)
         VALUES (NEW.account_id, TG_OP, NOW());
         RETURN NEW;
     END;
     $$ LANGUAGE plpgsql;
             

   • Creating the Trigger:

     CREATE TRIGGER transaction_trigger
     AFTER INSERT OR UPDATE ON transactions
     FOR EACH ROW
     EXECUTE FUNCTION log_transaction();
             

4. Testing the Trigger

   To test the trigger, you can insert or update a row in the transactions table and then check the transaction_audit table to ensure that the trigger has worked as expected.

   INSERT INTO transactions (account_id, amount) VALUES (1, 100.00);
         

   After running the above statement, you can verify that an entry has been added to transaction_audit.

This guide provides a basic understanding of triggers in PostgreSQL. As you become more familiar with triggers, you can explore more advanced scenarios and use cases.

INSERT Trigger

An INSERT trigger is fired automatically when a new record is inserted into the table.

Example:
    CREATE OR REPLACE FUNCTION log_new_employee()
    RETURNS TRIGGER AS $$
    BEGIN
    INSERT INTO employee_logs (action, employee_id, employee_name, salary)
    VALUES ('INSERT', NEW.id, NEW.name, NEW.salary);
    RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER log_new_employee_trigger
    AFTER INSERT ON employees
    FOR EACH ROW
    EXECUTE FUNCTION log_new_employee();
    

UPDATE Trigger

An UPDATE trigger is fired automatically when a record in the table is updated.

Example:
    CREATE OR REPLACE FUNCTION log_employee_update()
    RETURNS TRIGGER AS $$
    BEGIN
    INSERT INTO audit_trail (action, employee_id, old_salary, new_salary)
    VALUES ('UPDATE', OLD.id, OLD.salary, NEW.salary);
    RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER log_employee_update_trigger
    AFTER UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION log_employee_update();
    

DELETE Trigger

A DELETE trigger is fired automatically when a record is deleted from the table.

Example:
    CREATE OR REPLACE FUNCTION move_deleted_employee()
    RETURNS TRIGGER AS $$
    BEGIN
    INSERT INTO deleted_employees (employee_id, employee_name, deleted_at)
    VALUES (OLD.id, OLD.name, NOW());
    RETURN OLD;
    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER move_deleted_employee_trigger
    AFTER DELETE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION move_deleted_employee();