6.5 Triggers (BEFORE / AFTER)

Level: 6 | Version: v1.1 | Author: Meptrasoft

Overview

A trigger is a database object that automatically runs when a specified event occurs on a table or view.

Common events are:

INSERT
UPDATE
DELETE

Two common trigger timings are:

Type	Runs	Common Use
BEFORE	Before the change	Validation, changing values
AFTER	After the change	Logging, auditing

Trigger → Automatic database action

SQL Triggers — BEFORE vs AFTER: Automatic Database Actions — Figure 1: A trigger runs automatically in response to a database event; BEFORE triggers run before the change, while AFTER triggers run after the change.

BEFORE Trigger

A BEFORE trigger runs before the database change is completed.

It is commonly used for:

Validation
Adjusting values
Applying rules before insertion or update

PostgreSQL Example

First, create the trigger function:

CREATE OR REPLACE FUNCTION check_employee()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative';
    END IF;

    RETURN NEW;
END;
$$;

Then attach it to the table:

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION check_employee();

Now, an invalid value is rejected before the row is inserted.

BEFORE → Validate or modify before the change

AFTER Trigger

An AFTER trigger runs after the data change has successfully occurred.

It is commonly used for:

Auditing
Logging
Recording changes

Example concept:

UPDATE Employee
      ↓
Table Updated
      ↓
AFTER Trigger
      ↓
Audit Log

A PostgreSQL implementation can use a trigger function to record the change in an audit table.

AFTER → Perform an action after the change

BEFORE vs AFTER

BEFORE Trigger	AFTER Trigger
Runs before the change	Runs after the change
Useful for validation	Useful for auditing/logging
Can modify `NEW` values in applicable row-level triggers	Commonly records or reacts to completed changes

Easy Memory Trick

BEFORE → CHECK
AFTER  → LOG

Important PostgreSQL Terms

For row-level triggers, PostgreSQL provides special values such as:

NEW → New row value
OLD → Previous row value

For example:

NEW.salary

refers to the new salary value during a suitable row-level trigger.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
A trigger must be called manually	A trigger fires automatically when its event occurs
BEFORE means before the SQL statement is sent	It runs in response to the database event before the row change is completed
AFTER can validate data before insertion	Validation that must prevent the change is generally done before the change
Triggers are only used for errors	They can also be used for auditing, logging, and automatic actions
A trigger is the same as a function	In PostgreSQL, a trigger uses a trigger function but is a separate database object
Every database uses the same trigger syntax	Trigger syntax and capabilities vary by database system

Placement Quick Points

TRIGGER
→ AUTOMATIC DATABASE ACTION

BEFORE
→ VALIDATE / MODIFY

AFTER
→ LOG / AUDIT

A trigger runs automatically when its defined event occurs.
Common events are INSERT, UPDATE, and DELETE.
BEFORE triggers run before the row change is completed.
AFTER triggers run after the change.
PostgreSQL trigger functions are written using languages such as PL/pgSQL.
NEW and OLD can provide access to row values in applicable row-level triggers.

Interview Questions

What is a trigger?

A trigger is a database object that automatically executes in response to a specified database event.

What is a BEFORE trigger?

A BEFORE trigger runs before the associated row change and is commonly used for validation or modifying values.

What is an AFTER trigger?

An AFTER trigger runs after the associated change has occurred and is commonly used for logging or auditing.

What is the difference between a trigger and a trigger function?

The trigger defines when the automatic action fires. The trigger function contains the code that runs.

What are NEW and OLD?

For applicable row-level triggers:

NEW → New row values
OLD → Previous row values

Practice & Hands-On Exercises

Using the employees table:

Create a PostgreSQL BEFORE trigger that rejects a negative salary:

CREATE OR REPLACE FUNCTION check_employee()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative';
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION check_employee();

Test the trigger with a valid value.
Test it with an invalid value.
Explain the difference between BEFORE and AFTER triggers.
Give one real-world use case for an AFTER trigger.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

INSERT / UPDATE / DELETE
          ↓
       TRIGGER
       ↙     ↘
   BEFORE    AFTER
     ↓         ↓
 CHECK       LOG / AUDIT

A trigger automatically runs when a database event occurs; BEFORE triggers are useful for validation, while AFTER triggers are commonly used for logging and auditing.

End of Triggers (BEFORE / AFTER)