What is a Trigger in SQL?

A Trigger in SQL is a special type of stored program that automatically executes when a specific database event occurs.

In simple words:

A trigger automatically performs actions when INSERT, UPDATE, or DELETE operations happen on a table.

Why Triggers are Important

In enterprise applications:

Automatic validations are required
Audit logging is needed
Security checks are important
Business rules must execute automatically

Triggers help:

Automate database operations
Maintain data integrity
Track changes
Enforce business rules

Simple Real-Life Example

Think about:

ATM transaction alerts

When money is withdrawn:

SMS is automatically sent

Similarly:

SQL triggers automatically execute logic when database events occur.

What Events Can Trigger Execute On?

Triggers execute automatically during:

INSERT
UPDATE
DELETE

Trigger Internal Architecture

User Performs Database Operation
              |
              v
Trigger Event Detected
              |
              v
Trigger Executes Automatically
              |
              v
Additional Database Logic Runs

Types of Triggers in SQL

BEFORE INSERT Trigger
AFTER INSERT Trigger
BEFORE UPDATE Trigger
AFTER UPDATE Trigger
BEFORE DELETE Trigger
AFTER DELETE Trigger

1. BEFORE INSERT Trigger

Executes:

Before data is inserted

Use Cases

Validation
Default value generation
Data formatting

Example

CREATE TRIGGER before_student_insert

BEFORE INSERT
ON students

FOR EACH ROW

BEGIN

    SET NEW.name = UPPER(NEW.name);

END;

What Happens?

Before inserting:

naresh

Trigger converts it to:

NARESH

2. AFTER INSERT Trigger

Executes:

After data insertion

Use Cases

Audit logging
Notifications
Analytics updates

Example

CREATE TRIGGER after_student_insert

AFTER INSERT
ON students

FOR EACH ROW

BEGIN

    INSERT INTO student_logs(message)

    VALUES('New student added');

END;

What Happens?

Whenever new student is added:

Log entry automatically created

3. BEFORE UPDATE Trigger

Executes:

Before updating data

Use Cases

Validation checks
Prevent invalid updates

Example

CREATE TRIGGER before_salary_update

BEFORE UPDATE
ON employees

FOR EACH ROW

BEGIN

    IF NEW.salary < 0 THEN

        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';

    END IF;

END;

What Happens?

Trigger prevents:

Invalid salary updates

4. AFTER UPDATE Trigger

Executes:

After updating data

Use Cases

Audit tracking
History management
Notification systems

Example

CREATE TRIGGER after_salary_update

AFTER UPDATE
ON employees

FOR EACH ROW

BEGIN

    INSERT INTO salary_history(

        employee_id,
        old_salary,
        new_salary

    )

    VALUES(

        OLD.employee_id,
        OLD.salary,
        NEW.salary

    );

END;

Important Keywords

Keyword	Meaning
OLD	Previous row value
NEW	Updated row value

5. BEFORE DELETE Trigger

Executes:

Before deleting rows

Use Cases

Validation
Restriction checks

Example

CREATE TRIGGER before_employee_delete

BEFORE DELETE
ON employees

FOR EACH ROW

BEGIN

    IF OLD.role = 'ADMIN' THEN

        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Admin cannot be deleted';

    END IF;

END;

6. AFTER DELETE Trigger

Executes:

After deleting rows

Use Cases

Archive deleted records
Audit tracking

Example

CREATE TRIGGER after_employee_delete

AFTER DELETE
ON employees

FOR EACH ROW

BEGIN

    INSERT INTO deleted_employee_logs(

        employee_id,
        employee_name

    )

    VALUES(

        OLD.employee_id,
        OLD.employee_name

    );

END;

How Trigger Works Internally

Database Event Occurs
        |
        v
Trigger Automatically Activated
        |
        v
Trigger Logic Executes
        |
        v
Database Operation Completes

Trigger Execution Flow

INSERT / UPDATE / DELETE
            |
            v
Check Trigger Availability
            |
            v
Execute Trigger Logic
            |
            v
Continue Database Processing

FOR EACH ROW Meaning

Trigger executes:

Once for every affected row

Example

If:

UPDATE employees
SET salary = salary + 1000;

updates:

100 rows

Trigger runs:

100 times

Triggers vs Stored Procedures

Feature	Trigger	Stored Procedure
Execution	Automatic	Manual Call
Triggered By	Database Events	User/Application
Parameters	Not Supported Directly	Supported
Purpose	Automation	Reusable Logic

Triggers vs Constraints

Feature	Trigger	Constraint
Complex Logic	Supported	Limited
Automation	Yes	Basic Validation
Custom Actions	Possible	Not Possible

Real-Time Banking Example

Banking systems use triggers for:

Transaction logging
Fraud detection
Balance auditing

Example

When money is transferred:

Trigger stores transaction history automatically

Real-Time E-Commerce Example

E-commerce platforms use triggers for:

Inventory updates
Order logs
Stock alerts

Example

When order placed:

Product stock automatically decreases

Real-Time Learning Platform Example

Learning platforms use triggers for:

Student activity logs
Course completion tracking
Certificate generation

Example

When course completed:

Certificate record automatically created

Triggers in Microservices

Microservices sometimes use triggers for:

Audit logging
Event tracking
Change detection

Advantages of Triggers

Automatic execution
Improves data integrity
Supports auditing
Reduces duplicate logic
Centralized business rules

Disadvantages of Triggers

Difficult debugging
Hidden execution logic
Performance overhead
Complex maintenance

Performance Consideration

Too many triggers may:

Slow database operations
Increase transaction time

Why?

Because:

Additional SQL logic executes automatically

Best Practices

Keep trigger logic simple
Avoid heavy processing inside triggers
Use triggers mainly for auditing and validation
Document trigger behavior clearly

Common Interview Mistake

Many developers think:

Triggers are manually executed

Reality

Triggers:

Execute automatically when events occur

Professional Interview Answer

A Trigger in SQL is a special type of stored program that automatically executes when specific database events such as INSERT, UPDATE, or DELETE occur on a table. Triggers are commonly used for auditing, validation, logging, enforcing business rules, maintaining data integrity, and automating database operations. Triggers can execute before or after database events and support access to old and new row values using OLD and NEW keywords. They are widely used in enterprise systems such as banking platforms, e-commerce applications, analytics systems, and microservices architectures for automatic database event handling.

Why Interviewers Like This Answer

Clearly explains automatic execution concept
Includes OLD and NEW keyword usage
Shows enterprise auditing understanding
Provides real-world automation examples
Explains trigger internals and performance

Frequently Asked Questions