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

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

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

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

Hint

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

Practice

1. What is the main purpose of an AFTER trigger in PostgreSQL?

easy

A. To execute a function after the main database operation completes successfully

B. To prevent a database operation from happening

C. To execute a function before the database operation starts

D. To rollback the transaction if an error occurs

Solution

1. Step 1: Understand the timing of AFTER triggers

   AFTER triggers run only after the main database action (INSERT, UPDATE, DELETE) has completed successfully.

2. Step 2: Identify the purpose of AFTER triggers

   They are used to perform actions like logging or notifications after the main operation finishes.

3. Final Answer:

   To execute a function after the main database operation completes successfully -> Option A

4. Quick Check:

   AFTER trigger = runs after operation [OK]

Hint: AFTER triggers run only after successful main actions [OK]

Common Mistakes:

• Confusing AFTER with BEFORE triggers
• Thinking AFTER triggers can stop the main operation
• Assuming AFTER triggers run before the operation

2. Which of the following is the correct syntax to create an AFTER INSERT trigger in PostgreSQL?

easy

A. CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_name();

B. CREATE TRIGGER trg AFTER INSERT ON table_name EXECUTE FUNCTION func_name();

C. CREATE TRIGGER trg BEFORE INSERT ON table_name EXECUTE FUNCTION func_name();

D. CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH STATEMENT EXECUTE FUNCTION func_name();

Solution

1. Step 1: Recall the correct CREATE TRIGGER syntax

   PostgreSQL requires specifying FOR EACH ROW or FOR EACH STATEMENT for triggers.

2. Step 2: Identify the correct syntax for AFTER INSERT

   CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_name(); correctly uses AFTER INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_name();

3. Final Answer:

   CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_name(); -> Option A

4. Quick Check:

   AFTER INSERT + FOR EACH ROW + EXECUTE FUNCTION = CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_name(); [OK]

Hint: AFTER triggers need FOR EACH ROW or STATEMENT [OK]

Common Mistakes:

• Omitting FOR EACH ROW or FOR EACH STATEMENT
• Using BEFORE instead of AFTER
• Using EXECUTE PROCEDURE instead of EXECUTE FUNCTION in modern PostgreSQL

3. Given the following trigger and table, what will be the output after inserting a row?

CREATE TABLE users(id SERIAL PRIMARY KEY, name TEXT);
CREATE FUNCTION log_insert() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Inserted user: %', NEW.name;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER after_user_insert AFTER INSERT ON users FOR EACH ROW EXECUTE FUNCTION log_insert();

INSERT INTO users(name) VALUES ('Alice');

medium

A. No output, the insert happens silently

B. An error occurs because RETURN NEW is invalid in AFTER trigger

C. The insert is rolled back due to the trigger

D. A notice message: 'Inserted user: Alice'

Solution

1. Step 1: Understand the trigger function behavior

   The function raises a NOTICE with the inserted user's name after insert.

2. Step 2: Recognize AFTER trigger effects

   AFTER triggers run after the insert, so the notice will be shown, and the insert completes successfully.

3. Final Answer:

   A notice message: 'Inserted user: Alice' -> Option D

4. Quick Check:

   AFTER trigger raises notice = A notice message: 'Inserted user: Alice' [OK]

Hint: AFTER triggers can raise notices after insert [OK]

Common Mistakes:

• Thinking RETURN NEW is invalid in AFTER triggers
• Expecting no output from the trigger
• Assuming the insert is rolled back

4. You created an AFTER UPDATE trigger but it never fires. Which of the following is the most likely cause?

medium

A. The trigger was created as BEFORE UPDATE instead of AFTER UPDATE

B. The UPDATE statement does not change any column values

C. The trigger function does not return NEW or OLD

D. The trigger is defined FOR EACH STATEMENT but the function expects FOR EACH ROW

Solution

1. Step 1: Understand when AFTER UPDATE triggers fire

   AFTER UPDATE triggers fire only if the UPDATE actually changes data.

2. Step 2: Analyze the cause of no trigger firing

   If the UPDATE sets columns to their existing values, no actual change occurs, so the trigger does not fire.

3. Final Answer:

   The UPDATE statement does not change any column values -> Option B

4. Quick Check:

   UPDATE with no change = no AFTER trigger fire [OK]

Hint: AFTER UPDATE triggers fire only on actual data changes [OK]

Common Mistakes:

• Assuming triggers fire even if no data changes
• Confusing BEFORE and AFTER triggers
• Not matching trigger function with trigger type

5. You want to log every DELETE on a table after it happens, but only if the deleted row's status is 'active'. Which is the best way to implement this using an AFTER trigger?

hard

A. Create an AFTER DELETE trigger that logs all deletes without checking status

B. Create a BEFORE DELETE trigger that checks OLD.status and prevents deletion if not 'active'

C. Create an AFTER DELETE trigger that checks if OLD.status = 'active' inside the trigger function before logging

D. Create a BEFORE DELETE trigger that logs only if OLD.status = 'active'

Solution

1. Step 1: Understand the requirement for conditional logging after delete

   We want to log only after the delete happens and only for rows with status 'active'.

2. Step 2: Choose the correct trigger timing and condition

   AFTER DELETE trigger can access OLD row data and conditionally log if OLD.status = 'active'.

3. Final Answer:

   Create an AFTER DELETE trigger that checks if OLD.status = 'active' inside the trigger function before logging -> Option C

4. Quick Check:

   Conditional logging after delete = AFTER trigger with check [OK]

Hint: Use AFTER DELETE trigger with condition inside function [OK]

Common Mistakes:

• Using BEFORE trigger which runs before deletion
• Logging all deletes without condition
• Trying to prevent deletion instead of logging