SQL Triggers

SQL Triggers are special database objects that automatically execute when a specified event occurs on a table. They are commonly used to maintain data integrity, record changes, validate information, synchronize related data, and enforce database-level business rules.

Unlike a normal SQL statement or stored procedure, a trigger is activated automatically by a database event such as INSERT, UPDATE, or DELETE. The application does not normally need to call the trigger explicitly.

SQL Triggers are widely used in banking systems, e-commerce applications, educational portals, healthcare applications, inventory systems, and enterprise database solutions.

In this tutorial, we will understand SQL Triggers from basic concepts to practical examples, including trigger syntax, trigger types, BEFORE and AFTER triggers, OLD and NEW values, audit logging, data validation, advantages, limitations, and interview questions.

What is an SQL Trigger?

An SQL Trigger is a database object that automatically executes one or more SQL statements when a specified event occurs on a table or, depending on the database system, another database object.

For example, when a new employee is inserted into an Employee table, a trigger can automatically create an entry in an Employee_Audit table.

Simple Definition

A trigger is an automatic database program that executes in response to a specified database event.

Why Are SQL Triggers Used?

Triggers are mainly used when an action needs to happen automatically whenever data changes.

Automatically validate data.
Maintain audit records.
Track INSERT, UPDATE, and DELETE operations.
Maintain data consistency between related tables.
Implement selected database-level business rules.
Automatically record important changes.
Reduce repetitive application-side database logic.
Support database auditing and monitoring.

How Do SQL Triggers Work?

A trigger generally involves three important components:

Component	Description
Event	The database operation that activates the trigger, such as INSERT, UPDATE, or DELETE.
Timing	Specifies when the trigger executes, such as BEFORE or AFTER, depending on the database system.
Action	The SQL statements executed automatically by the trigger.

For example, an AFTER INSERT trigger can automatically insert a record into an audit table whenever a new employee is added.

Basic Syntax of SQL Trigger

Trigger syntax differs between database management systems. The following example uses MySQL-style syntax.

DELIMITER //

CREATE TRIGGER trigger_name
AFTER INSERT
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END//

DELIMITER ;

Important Parts of the Syntax

CREATE TRIGGER: Creates a new trigger.
trigger_name: Name assigned to the trigger.
BEFORE / AFTER: Specifies when the trigger executes.
INSERT / UPDATE / DELETE: Specifies the event that activates the trigger.
ON table_name: Specifies the table associated with the trigger.
FOR EACH ROW: Specifies that the trigger executes for each affected row in MySQL.
BEGIN ... END: Contains the SQL statements executed by the trigger.

The DELIMITER command is commonly used in MySQL clients so that semicolons inside the trigger body are not interpreted as the end of the CREATE TRIGGER statement.

Types of SQL Triggers

Triggers can be classified according to their timing and the database event that activates them.

Trigger Type	Description
BEFORE INSERT	Executes before a new row is inserted.
AFTER INSERT	Executes after a new row is inserted.
BEFORE UPDATE	Executes before an existing row is updated.
AFTER UPDATE	Executes after an existing row is updated.
BEFORE DELETE	Executes before an existing row is deleted.
AFTER DELETE	Executes after an existing row is deleted.

The exact trigger capabilities and syntax depend on the database management system. Always check the documentation of the specific DBMS being used.

BEFORE Trigger

A BEFORE trigger executes before the associated database operation is completed. It is commonly used for validation and, in database systems that support it, modifying values before they are stored.

Example: BEFORE INSERT Trigger

DELIMITER //

CREATE TRIGGER check_salary
BEFORE INSERT
ON Employee
FOR EACH ROW
BEGIN

    IF NEW.salary < 0 THEN
        SET NEW.salary = 0;
    END IF;

END//

DELIMITER ;

In this MySQL example, if a negative salary is supplied, the trigger changes the value to zero before the row is inserted.

In many real-world applications, rejecting invalid data with an error may be preferable to silently changing it. The correct approach depends on the business requirement.

AFTER Trigger

An AFTER trigger executes after the associated database operation has successfully occurred. AFTER triggers are commonly used for logging, auditing, and updating related information.

Example: AFTER INSERT Trigger

DELIMITER //

CREATE TRIGGER employee_log
AFTER INSERT
ON Employee
FOR EACH ROW
BEGIN

    INSERT INTO Employee_Audit
    VALUES
    (NEW.id, NEW.name, NOW());

END//

DELIMITER ;

Whenever a new employee is inserted, the trigger automatically records the employee information in the audit table.

INSERT Trigger

An INSERT trigger executes when a new row is inserted into a table.

Example

DELIMITER //

CREATE TRIGGER customer_register
AFTER INSERT
ON Customer
FOR EACH ROW
BEGIN

    INSERT INTO Customer_Log
    VALUES
    (NEW.customer_id, 'New Customer Added');

END//

DELIMITER ;

When a new customer is registered, the trigger automatically creates a corresponding log entry.

Common Applications

Customer registration logging.
Employee creation auditing.
Order creation tracking.
Student registration records.
System activity monitoring.

UPDATE Trigger

An UPDATE trigger executes when an existing row is modified.

It is especially useful for maintaining history tables and tracking changes to important information.

Example

DELIMITER //

CREATE TRIGGER salary_update
AFTER UPDATE
ON Employee
FOR EACH ROW
BEGIN

    INSERT INTO Salary_History
    VALUES
    (OLD.id, OLD.salary, NEW.salary);

END//

DELIMITER ;

When an employee's salary changes, the trigger stores the previous and updated salary values in the Salary_History table.

DELETE Trigger

A DELETE trigger executes when a row is deleted from a table.

DELETE triggers are commonly used to preserve information about deleted records or maintain audit logs.

Example

DELIMITER //

CREATE TRIGGER employee_delete
BEFORE DELETE
ON Employee
FOR EACH ROW
BEGIN

    INSERT INTO Deleted_Employee
    VALUES
    (OLD.id, OLD.name);

END//

DELIMITER ;

Before an employee is deleted, the trigger stores selected information in the Deleted_Employee table.

OLD and NEW Keywords in SQL Triggers

In row-level triggers, OLD and NEW are commonly used to access values associated with the affected row. Their availability depends on the trigger event and the database system.

Keyword	Meaning	Common Usage
NEW	Represents the new row values.	INSERT and UPDATE
OLD	Represents the previous row values.	UPDATE and DELETE

Example

UPDATE Employee
SET salary = 50000
WHERE id = 101;

For an UPDATE trigger, OLD.salary represents the previous salary and NEW.salary represents the new salary.

OLD and NEW Example

DELIMITER //

CREATE TRIGGER salary_audit
AFTER UPDATE
ON Employee
FOR EACH ROW
BEGIN

    INSERT INTO Salary_Audit
    VALUES
    (
        OLD.id,
        OLD.salary,
        NEW.salary,
        NOW()
    );

END//

DELIMITER ;

This trigger records the employee ID, previous salary, new salary, and time of modification.

Row-Level Triggers

A row-level trigger executes separately for each row affected by an operation.

For example, if an UPDATE statement modifies 100 rows and the database system supports a FOR EACH ROW trigger, the trigger logic can execute for each affected row.

UPDATE Employee
SET Department = 'IT'
WHERE Department = 'Computer Science';

If 50 employees satisfy the condition, a row-level trigger associated with this UPDATE operation can execute for each affected employee.

Statement-Level Triggers

A statement-level trigger executes once for an entire SQL statement, regardless of the number of rows affected.

However, trigger behavior differs significantly between database systems. For example, MySQL supports row-level triggers with FOR EACH ROW rather than traditional statement-level triggers.

Therefore, statement-level trigger concepts should always be studied according to the specific DBMS being used.

Practical Example: Employee Audit System

Suppose an organization wants to maintain an automatic record whenever a new employee is added.

Employee Table

CREATE TABLE Employee
(
    id INT,
    name VARCHAR(50),
    salary INT
);

Audit Table

CREATE TABLE Employee_Audit
(
    employee_id INT,
    employee_name VARCHAR(50),
    action_time DATETIME
);

Trigger

DELIMITER //

CREATE TRIGGER employee_insert_log
AFTER INSERT
ON Employee
FOR EACH ROW
BEGIN

    INSERT INTO Employee_Audit
    VALUES
    (
        NEW.id,
        NEW.name,
        NOW()
    );

END//

DELIMITER ;

Whenever a new employee is inserted, the trigger automatically creates an audit record.

Creating a SQL Trigger

The CREATE TRIGGER statement is used to create a trigger. The exact syntax depends on the database system.

MySQL Example

DELIMITER //

CREATE TRIGGER order_insert_log
AFTER INSERT
ON Orders
FOR EACH ROW
BEGIN

    INSERT INTO Order_Log
    VALUES
    (
        NEW.order_id,
        NOW()
    );

END//

DELIMITER ;

This trigger records the creation time of a new order.

Viewing Existing Triggers

In MySQL, the following command can be used to display triggers available in the current database.

SHOW TRIGGERS;

The result provides information about triggers such as their name, event, timing, and associated table.

Removing a Trigger

The DROP TRIGGER statement is used to remove a trigger that is no longer required.

Syntax

DROP TRIGGER trigger_name;

Example

DROP TRIGGER employee_log;

This removes the specified trigger from the database.

Updating a Trigger

Trigger modification syntax differs between database systems. In MySQL, a common approach is to drop the existing trigger and create it again with the required definition.

DROP TRIGGER IF EXISTS salary_update;

DELIMITER //

CREATE TRIGGER salary_update
AFTER UPDATE
ON Employee
FOR EACH ROW
BEGIN

    INSERT INTO Salary_History
    VALUES
    (
        OLD.id,
        OLD.salary,
        NEW.salary
    );

END//

DELIMITER ;

BEFORE Trigger vs AFTER Trigger

Feature	BEFORE Trigger	AFTER Trigger
Execution	Before the associated operation	After the associated operation
Common Purpose	Validation or preparing values	Logging and related actions
Example	Checking input values	Creating audit records
Typical Use	Data preparation	Auditing and synchronization

SQL Trigger with Audit Table

Auditing is one of the most common practical applications of database triggers. An audit table can store information about important changes made to database records.

Audit Table

CREATE TABLE Salary_Audit
(
    Employee_ID INT,
    Old_Salary INT,
    New_Salary INT,
    Changed_Date DATETIME
);

Trigger

DELIMITER //

CREATE TRIGGER salary_change
AFTER UPDATE
ON Employee
FOR EACH ROW
BEGIN

    INSERT INTO Salary_Audit
    VALUES
    (
        OLD.id,
        OLD.salary,
        NEW.salary,
        NOW()
    );

END//

DELIMITER ;

Whenever the employee salary changes, the previous and new salary values are automatically recorded.

SQL Trigger for Data Validation

Triggers can be used for certain validation requirements before data is stored.

Example

Suppose an application requires the employee age to be at least 18.

DELIMITER //

CREATE TRIGGER check_age
BEFORE INSERT
ON Employee
FOR EACH ROW
BEGIN

    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Employee age must be at least 18';
    END IF;

END//

DELIMITER ;

In this example, the trigger rejects an INSERT when the supplied age is below 18.

For simple rules such as minimum or maximum values, database constraints may be preferable when supported by the DBMS. Triggers are more suitable when the validation requires additional database logic.

SQL Trigger in Banking Applications

Banking systems require accurate records of financial operations. Triggers can be used as one component of an auditing strategy for tracking changes.

Example

DELIMITER //

CREATE TRIGGER account_balance_log
AFTER UPDATE
ON Account
FOR EACH ROW
BEGIN

    INSERT INTO Account_History
    VALUES
    (
        NEW.account_id,
        OLD.balance,
        NEW.balance,
        NOW()
    );

END//

DELIMITER ;

This example records the previous and updated account balance whenever the Account row is modified.

In real banking systems, financial transactions require much more comprehensive transaction controls and auditing mechanisms than a single trigger.

SQL Trigger in E-Commerce Systems

E-commerce applications can use triggers for selected inventory and auditing operations.

Example

DELIMITER //

CREATE TRIGGER update_stock
AFTER INSERT
ON Orders
FOR EACH ROW
BEGIN

    UPDATE Product
    SET Quantity = Quantity - NEW.Quantity
    WHERE Product_ID = NEW.Product_ID;

END//

DELIMITER ;

This example reduces product inventory when a new order record is inserted.

In a production application, stock management should also consider transactions, concurrency, insufficient inventory, cancelled orders, and multiple order items.

SQL Trigger in Student Management System

Educational systems can use triggers to maintain activity and audit records.

Example

DELIMITER //

CREATE TRIGGER student_registration
AFTER INSERT
ON Student
FOR EACH ROW
BEGIN

    INSERT INTO Student_Log
    VALUES
    (
        NEW.Student_ID,
        'Registered',
        NOW()
    );

END//

DELIMITER ;

Whenever a student is registered, the system automatically creates an activity record.

Triggers vs Stored Procedures

Triggers and stored procedures can both contain SQL logic, but their execution models are different.

Feature	Trigger	Stored Procedure
Execution	Automatically activated by an event	Normally called explicitly
Parameters	Trigger parameters are not used like procedure parameters	Can support parameters
Purpose	Automatic event-based actions	Reusable database operations
Activation	INSERT, UPDATE, DELETE or supported events	Explicit procedure call

Triggers vs Constraints

Feature	Triggers	Constraints
Purpose	Execute automatic database logic	Enforce data integrity rules
Complex Logic	Can implement more complex actions	Generally designed for declarative integrity rules
Execution	Activated by specified database events	Checked automatically by the DBMS
Examples	Audit logging and synchronization	PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK

For simple integrity rules, constraints should generally be considered before triggers because constraints are specifically designed for enforcing data integrity.

Advantages of SQL Triggers

Automate database operations.
Help maintain data consistency.
Support audit logging.
Reduce repetitive application-side logic.
Can enforce selected business rules.
Automatically track changes.
Useful for maintaining related information.
Centralize certain database-level operations.

Limitations of SQL Triggers

Can make database behavior harder to understand.
Complex triggers may affect performance.
Debugging can be difficult.
Trigger behavior is not always visible in application code.
Syntax and capabilities differ between database systems.
Too many triggers can increase maintenance complexity.
Poorly designed triggers can create unexpected side effects.

Best Practices for SQL Triggers

Use triggers only when they provide a clear benefit.
Keep trigger logic short and easy to understand.
Use meaningful trigger names.
Document the purpose of each trigger.
Avoid unnecessary triggers on heavily used tables.
Test triggers with INSERT, UPDATE, and DELETE scenarios where applicable.
Monitor database performance.
Use constraints instead of triggers when a simple constraint can solve the problem.
Consider transaction behavior before using triggers for important operations.
Understand the trigger behavior of the selected DBMS.

Common Mistakes While Using SQL Triggers

Creating triggers when a constraint would be sufficient.
Writing unnecessarily complex trigger logic.
Ignoring performance implications.
Not documenting automatic database behavior.
Using incorrect OLD or NEW references.
Forgetting database-specific trigger syntax.
Creating recursive or unintended trigger interactions.
Not testing rollback and error scenarios.

Performance Considerations of SQL Triggers

Triggers execute automatically, so their queries become part of the database operation that activates them. Poorly designed trigger logic can therefore increase execution time.

Avoid unnecessary queries inside triggers.
Use appropriate indexes.
Keep trigger operations simple.
Avoid duplicate trigger logic.
Test triggers using realistic amounts of data.
Review execution plans when appropriate.
Monitor database performance after introducing triggers.

When Should You Use SQL Triggers?

Triggers are useful when an action must consistently happen at the database level whenever a specific data event occurs.

Common situations include:

Audit logging.
Change history.
Automatic activity records.
Selected validation requirements.
Maintaining related data.
Database-level business rules.

When Should You Avoid SQL Triggers?

Triggers are not always the best solution. They should be avoided when they unnecessarily hide application logic or create significant maintenance and performance problems.

When a simple constraint can enforce the rule.
When the same logic is easier to maintain in application code.
When triggers would create complicated dependencies.
When automatic execution could produce unexpected side effects.
When performance testing shows unacceptable overhead.

SQL Triggers Interview Questions and Answers

1. What is an SQL Trigger?

An SQL Trigger is a database object that automatically executes when a specified database event occurs.

2. When does a trigger execute?

It executes automatically when its associated event occurs, such as INSERT, UPDATE, or DELETE.

3. What are BEFORE and AFTER triggers?

A BEFORE trigger executes before the associated operation, while an AFTER trigger executes after the operation.

4. What is an INSERT trigger?

An INSERT trigger is activated when a new row is inserted into a table.

5. What is an UPDATE trigger?

An UPDATE trigger is activated when an existing row is modified.

6. What is a DELETE trigger?

A DELETE trigger is activated when a row is deleted.

7. What are OLD and NEW in triggers?

OLD generally represents previous row values, while NEW represents new row values where supported by the trigger event and DBMS.

8. Can OLD be used with INSERT?

No. An INSERT operation does not have an old row value.

9. Can NEW be used with DELETE?

No. A DELETE operation removes an existing row and therefore provides the OLD value rather than a NEW value.

10. What is an audit trigger?

An audit trigger automatically records database changes in an audit or history table.

11. Can triggers accept parameters?

Triggers are not called like stored procedures and generally do not accept parameters in the same way procedures do.

12. What is the difference between a trigger and a stored procedure?

A trigger is automatically activated by a database event, while a stored procedure is normally executed explicitly.

13. Can a trigger be deleted?

Yes. In MySQL, the DROP TRIGGER statement can be used to remove a trigger.

14. Are SQL triggers database-specific?

Yes. Trigger syntax, supported features, timing options, and behavior can differ between DBMS products.

15. What are the disadvantages of triggers?

Complex triggers can increase database complexity, make debugging harder, and affect performance.

16. Can triggers validate data?

Yes. Triggers can implement certain validation logic, although constraints are often preferable for simple integrity rules.

17. What events commonly activate SQL triggers?

INSERT, UPDATE, and DELETE are common trigger events in relational database systems.

18. Why are triggers used?

Triggers are used to automate database actions, maintain audit information, and enforce selected database-level rules.

19. What is a row-level trigger?

A row-level trigger executes separately for each row affected by the triggering operation.

20. Why should triggers be optimized?

Because trigger logic becomes part of the database operation that activates it and can affect overall performance.

21. What command is used to create a trigger in MySQL?

The CREATE TRIGGER statement is used to create a trigger.

22. What command can display MySQL triggers?

The SHOW TRIGGERS command displays triggers in the current MySQL database.

23. What command removes a trigger?

The DROP TRIGGER command removes an existing trigger.

24. Why is DELIMITER used in MySQL trigger examples?

DELIMITER allows the MySQL client to distinguish the end of the complete CREATE TRIGGER statement from semicolons used inside the trigger body.

25. Can triggers be used for auditing?

Yes. Audit triggers can automatically record changes such as INSERT, UPDATE, or DELETE operations.

26. Can triggers improve data consistency?

They can help maintain consistency when automatic database-level actions are required.

27. Are triggers always better than application logic?

No. The appropriate approach depends on the application architecture, business requirements, performance, and maintainability.

28. Are triggers always better than constraints?

No. For simple integrity rules, constraints are generally more direct and easier to maintain.

29. What is the major benefit of SQL Triggers?

Their major benefit is automatic execution of database logic in response to specified data events.

30. Why are SQL Triggers important?

SQL Triggers are important because they can automate auditing, validation, change tracking, and other database-level operations.

Conclusion

SQL Triggers are powerful database objects that allow specific SQL operations to execute automatically when defined database events occur. They are particularly useful for audit logging, change tracking, selected validation requirements, and maintaining related database information.

The most commonly studied trigger events are INSERT, UPDATE, and DELETE, while important trigger concepts include BEFORE triggers, AFTER triggers, OLD values, NEW values, row-level execution, audit triggers, and trigger management.

However, triggers should be designed carefully. Excessive or complicated trigger logic can make a database difficult to maintain and may affect performance. For simple integrity requirements, constraints are often a better choice.

By understanding SQL Trigger syntax, types, practical applications, advantages, limitations, and best practices, students and developers can build stronger database concepts for academic examinations, interviews, and real-world database development.

← Previous: Subqueries Next: SQL Interview Questions →