SQL Triggers – Quipoin

Triggers

Videos Discussion

In most SQL operations, we explicitly tell the database what to do.

We write an INSERT, UPDATE, or DELETE statement, and the database executes it exactly as instructed.

But in real-world systems, this is often not enough.

Consider situations like:

Automatically recording who updated a record

Preventing invalid data from being inserted

Logging changes for auditing

Updating related tables when data changes

In such cases, we do not want developers to remember to write extra queries every time.

Instead, we want the database itself to react automatically whenever a data change happens.

This is where Triggers come into play.

What Is a Trigger?

A trigger is a special database object that:

Is automatically executed

Fires in response to an INSERT, UPDATE, or DELETE

Is attached to a table or view

Runs without being explicitly called

In simple words:

A trigger is SQL logical that runs automatically when a data change event occurs.

Why Triggers Are Needed

Triggers are commonly used to:

Enforce business rules

Maintain data integrity

Perform automatic logging

Synchronize related tables

Prevent invalid operations

They help move critical logic closer to the database, making systems more reliable.

Trigger Timing: BEFORE vs AFTER

One of the most important concepts in triggers is when they execute.

Based on timing, triggers are mainly of two types:

BEFORE Triggers

AFTER Triggers

Understanding this difference is crucial for correct design.

BEFORE Triggers

A BEFORE trigger executes before the actual data modification happens.

That means:

The database checks the trigger logic first

Only if the trigger allows it, the operation continues

When BEFORE Triggers Are Used

BEFORE triggers are ideal when:

Data needs to be validated

Values need to be modified before saving

Invalid operations must be stopped

They act like a gatekeeper.

Example:

Scenario: Suppose we want to prevent inserting an employee with a negative salary.

BEFORE INSERT Trigger

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END;

Here:

The trigger runs before insertion

Invalid data is blocked

The record is never saved

Key Characteristics of BEFORE Triggers

Execute before data change

Can modify NEW values

Can stop the operation

Best for validation and correction

AFTER Triggers

An AFTER trigger executes after the data modification is completed successfully.

This means:

The data is already saved

The trigger reacts to the change

It cannot stop the original operation

When AFTER Triggers Are Used

AFTER triggers are best when:

Logging is required

Audit records must be maintained

Related tables need updates

Notifications need to be triggered

They act like a reaction system.

Example:

Scenario: Suppose we want to log every salary update.

AFTER UPDATE Trigger

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit(emp_id, old_salary, new_salary, updated_at)
    VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
END;

Here:

The update happens first

The trigger logs the change

Original data remains untouched

Key Characteristics of AFTER Triggers

Execute after data change

Cannot modify NEW values

Cannot stop the operation

Ideal for auditing and logging

Comparison Between BEFORE and AFTER

Feature	BEFORE Trigger	AFTER Trigger
Execution time	Before operation	After operation
Can stop operation	Yes	No
Can modify data	Yes	No
Common use cases	Validation, correction	Logging, auditing
Risk level	Medium	Lower

Real-World Use Cases

In real applications:

BEFORE triggers validate and sanitize data

AFTER triggers maintain logs and history

Both together enforce strong data consistency

Well-designed systems often use both types strategically.

Things to Be Careful About

While triggers are powerful, misuse can cause issues:

Hidden logic makes debugging harder

Performance impact if overused

Recursive triggers can cause errors

Business logic duplication between app and DB

Triggers should be minimal, focused, and well-documented.

Two Minute Drill

Triggers run automatically
BEFORE triggers run before data changes
AFTER triggers run after data changes
BEFORE is for validation
AFTER is for logging
Triggers improve data integrity but must be used wisely