PostgreSQL Trigger Automation

Introduction

In this lab, you will learn how to automate tasks in PostgreSQL using triggers. The lab focuses on creating a trigger function using PL/pgSQL to automatically log salary changes in an employees table to a separate salary_changes table.

You'll be guided through writing the trigger function, binding it to a table event (specifically, an update on the employees table), testing the trigger's behavior, and finally, disabling and dropping the trigger.

Skills Graph

Create Tables and the Trigger Function

In this step, you will create the employees and salary_changes tables, and then define the log_salary_change function using PL/pgSQL.

First, connect to the PostgreSQL database as the postgres user:

sudo -u postgres psql

Now, create the employees table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

This command creates a table with an id (primary key), name, and salary column.

Next, create the salary_changes table to store the history of salary changes:

CREATE TABLE salary_changes (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_date TIMESTAMP
);

Now, let's write the trigger function using PL/pgSQL. This function will be executed before an update operation on the employees table. It will record the old and new salary values in the salary_changes table.

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This code defines a function named log_salary_change. It checks if the salary has changed. If it has, it inserts a new row into the salary_changes table with the employee ID, old salary, new salary, and the current timestamp.

Create the Trigger

In this step, you will bind the log_salary_change function to the employees table so that it is automatically executed when a salary update occurs.

Create the trigger using the following SQL command:

CREATE TRIGGER salary_change_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

This command creates a trigger named salary_change_trigger. It specifies that the trigger should be activated before an UPDATE operation on the employees table. The FOR EACH ROW clause indicates that the trigger function should be executed for each row that is updated.

Test the Trigger

In this step, you will test the behavior of the trigger. You'll insert data into the employees table and then update the salary to see if the trigger correctly logs the changes in the salary_changes table.

First, insert some initial data into the employees table:

INSERT INTO employees (name, salary) VALUES ('Alice', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Bob', 60000.00);

Now, update Alice's salary:

UPDATE employees SET salary = 55000.00 WHERE name = 'Alice';

To verify that the trigger worked correctly, query the salary_changes table:

SELECT * FROM salary_changes;

You should see a row in the salary_changes table with the information about Alice's salary change.

Next, update Bob's salary to the same value it already has:

UPDATE employees SET salary = 60000.00 WHERE name = 'Bob';

Since the salary is not actually changing, the trigger should not insert a new row into the salary_changes table. Let's verify this:

SELECT * FROM salary_changes;

You should still only see the one row related to Alice's salary change.

Finally, insert a new employee:

INSERT INTO employees (name, salary) VALUES ('Charlie', 70000.00);

Inserting a new employee should not trigger the log_salary_change trigger, as it's only set to fire on UPDATE events. Let's confirm this by checking the salary_changes table again:

SELECT * FROM salary_changes;

You should still only see the one row related to Alice's salary change.

Disable and Drop the Trigger

In this step, you will learn how to disable and drop a trigger in PostgreSQL.

First, disable the salary_change_trigger:

ALTER TABLE employees DISABLE TRIGGER salary_change_trigger;

Now, update Alice's salary again:

UPDATE employees SET salary = 60000.00 WHERE name = 'Alice';

Check the salary_changes table:

SELECT * FROM salary_changes;

You should still only see the one row from the previous update. This confirms that the trigger is disabled.

To re-enable the trigger, you can use the following command:

ALTER TABLE employees ENABLE TRIGGER salary_change_trigger;

Finally, to permanently remove the trigger, drop it using the following command:

DROP TRIGGER salary_change_trigger ON employees;

Summary

In this lab, you learned how to automate tasks in PostgreSQL using triggers. You created a trigger function using PL/pgSQL to log salary changes in an employees table to a salary_changes table. You wrote the function, bound it to a table event, tested it, and disabled/dropped it.