AFTER trigger behavior in PostgreSQL - Mini Project: Build & Apply

Create an AFTER Trigger in PostgreSQL

📖 Scenario: You are managing a simple employee database. You want to keep track of every new employee added by recording their name and the time they were added into a separate audit table.

🎯 Goal: Build an AFTER INSERT trigger in PostgreSQL that automatically inserts a record into an audit table whenever a new employee is added.

📋 What You'll Learn

Create a table called employees with columns id (integer primary key) and name (text).

Create a table called employee_audit with columns employee_name (text) and added_at (timestamp).

Create a trigger function called log_employee_insert that inserts the new employee's name and current timestamp into employee_audit.

Create an AFTER INSERT trigger called after_employee_insert on the employees table that calls the log_employee_insert function.

💡 Why This Matters

🌍 Real World

AFTER triggers are used in databases to automatically perform actions like logging, auditing, or updating related tables after data changes happen.

💼 Career

Database developers and administrators use triggers to enforce business rules and maintain data integrity without manual intervention.

1

Create the employees and employee_audit tables

Create a table called employees with columns id as integer primary key and name as text. Also create a table called employee_audit with columns employee_name as text and added_at as timestamp.

PostgreSQL

-- Create the employees table
-- Create the employee_audit table
-- Your code here

Need a hint?

Use CREATE TABLE statements with the exact column names and types.

2

Create the trigger function log_employee_insert

Create a trigger function called log_employee_insert that inserts the new employee's name and the current timestamp using NOW() into the employee_audit table. The function should return NEW.

PostgreSQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE employee_audit (
    employee_name TEXT,
    added_at TIMESTAMP
);

-- Create the trigger function log_employee_insert
-- Your code here

Need a hint?

Use CREATE OR REPLACE FUNCTION with RETURNS TRIGGER and write the body in PL/pgSQL.

3

Create the AFTER INSERT trigger after_employee_insert

Create an AFTER INSERT trigger called after_employee_insert on the employees table that calls the log_employee_insert function.

PostgreSQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE employee_audit (
    employee_name TEXT,
    added_at TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_employee_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO employee_audit(employee_name, added_at) VALUES (NEW.name, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the AFTER INSERT trigger after_employee_insert on employees
-- Your code here

Need a hint?

Use CREATE TRIGGER with AFTER INSERT ON employees and EXECUTE FUNCTION log_employee_insert().

4

Test the trigger by inserting a new employee

Insert a new employee with id 1 and name 'Alice' into the employees table to test the AFTER INSERT trigger.

PostgreSQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE employee_audit (
    employee_name TEXT,
    added_at TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_employee_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO employee_audit(employee_name, added_at) VALUES (NEW.name, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_insert();

-- Insert a new employee with id 1 and name 'Alice'
-- Your code here

Need a hint?

Use a simple INSERT INTO employees statement with the exact values.