SQL Topics
Triggers in SQL
title: Triggers in SQL
In many database applications, certain actions must happen automatically whenever data changes.
Examples:
- Record every salary update
- Maintain audit logs
- Validate inserted data
- Track deleted records
- Send notifications after specific operations
- Automatically update timestamps
Without automation:
Application Must Execute
Extra SQL Statementsfor every operation.
This increases complexity and the possibility of errors.
To solve this problem, SQL provides Triggers.
A Trigger is a special database object that automatically executes when a specified event occurs in a table or view.
Triggers help enforce business rules, maintain data integrity, and automate repetitive tasks.
What is a Trigger?
A Trigger is a stored database program that automatically runs when a specific event occurs.
Think of a trigger as:
Event Occurs
↓
Trigger Fires
↓
SQL Executes AutomaticallyUnlike stored procedures:
Stored Procedure
↓
Executed ManuallyTriggers:
Execute AutomaticallyWhy Are Triggers Important?
Imagine a banking system.
Requirement:
Whenever Balance Changes
↓
Store Old Value
Store New Value
Store Change TimeWithout triggers:
Application Must Handle LoggingWith triggers:
Database Handles It AutomaticallyThis improves consistency and reliability.
How Triggers Work
Process:
INSERT
UPDATE
DELETE
↓
Trigger Activated
↓
Execute SQL Logic
↓
Operation CompletesThe user does not need to execute the trigger directly.
Types of Triggers
Most database systems support:
BEFORE Trigger
AFTER Trigger
INSTEAD OF Trigger
DDL Trigger
DML TriggerEach serves a different purpose.
DML Triggers
DML (Data Manipulation Language) triggers respond to:
INSERT
UPDATE
DELETEoperations.
These are the most commonly used triggers.
DDL Triggers
DDL (Data Definition Language) triggers respond to:
CREATE
ALTER
DROPoperations.
Used mainly for database administration and security.
BEFORE Trigger
A BEFORE Trigger executes:
Before Data Is ModifiedExample:
Validate Data
Before INSERTCommon uses:
Data Validation
Business Rules
Default ValuesAFTER Trigger
An AFTER Trigger executes:
After Data Is ModifiedCommon uses:
Audit Logging
Notifications
ReportingINSTEAD OF Trigger
An INSTEAD OF Trigger:
Replaces Original OperationThe trigger executes instead of the requested INSERT, UPDATE, or DELETE.
Often used with:
ViewsBasic Trigger Syntax
Example (SQL Server):
CREATE TRIGGER TriggerName
ON TableName
AFTER INSERT
AS
BEGIN
SQL Statements
END;Understanding Trigger Components
CREATE TRIGGER
Creates a trigger.
TriggerName
Unique trigger identifier.
ON TableName
Table being monitored.
AFTER INSERT
Defines triggering event.
BEGIN...END
Contains trigger logic.
Creating Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Insert sample data:
INSERT INTO Employees
VALUES
(1,'Rahul',50000);First Trigger Example
Create audit table:
CREATE TABLE EmployeeAudit (
AuditMessage VARCHAR(255)
);Create trigger:
CREATE TRIGGER TRG_EmployeeInsert
ON Employees
AFTER INSERT
AS
BEGIN
INSERT INTO EmployeeAudit
VALUES
('New Employee Added');
END;Testing the Trigger
Insert employee:
INSERT INTO Employees
VALUES
(2,'Priya',60000);Automatically:
TRG_EmployeeInsertexecutes.
Audit table:
| AuditMessage |
|---|
| ------------- |
| New Employee Added |
UPDATE Trigger Example
Requirement:
Track Salary ChangesCreate audit table:
CREATE TABLE SalaryAudit (
EmployeeID INT,
OldSalary DECIMAL(10,2),
NewSalary DECIMAL(10,2)
);Create trigger:
CREATE TRIGGER TRG_SalaryUpdate
ON Employees
AFTER UPDATE
AS
BEGIN
INSERT INTO SalaryAudit
SELECT
D.EmployeeID,
D.Salary,
I.Salary
FROM deleted D
JOIN inserted I
ON D.EmployeeID =
I.EmployeeID;
END;Understanding INSERTED and DELETED Tables
SQL Server provides:
INSERTED
DELETEDtemporary tables.
INSERTED
Contains new values.
DELETED
Contains old values.
Useful during:
UPDATE
DELETEoperations.
DELETE Trigger Example
Create audit table:
CREATE TABLE DeletedEmployees (
EmployeeID INT,
EmployeeName VARCHAR(100)
);Trigger:
CREATE TRIGGER TRG_EmployeeDelete
ON Employees
AFTER DELETE
AS
BEGIN
INSERT INTO DeletedEmployees
SELECT *
FROM deleted;
END;Whenever a row is deleted:
Deleted Record Storedautomatically.
BEFORE Trigger Example (MySQL)
CREATE TRIGGER TRG_CheckSalary
BEFORE INSERT
ON Employees
FOR EACH ROW
BEGIN
IF NEW.Salary < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT =
'Invalid Salary';
END IF;
END;Negative salaries are prevented.
Trigger with Validation
Example:
Prevent Age Below 18Trigger validates data before insertion.
Useful for enforcing business rules.
Trigger with Automatic Timestamp
Table:
CREATE TABLE Orders (
OrderID INT,
CreatedAt DATETIME
);Trigger:
CREATE TRIGGER TRG_OrderTimestamp
BEFORE INSERT
ON Orders
FOR EACH ROW
SET NEW.CreatedAt = NOW();Timestamp generated automatically.
Trigger with Logging
Example:
Track Every UpdateTrigger writes:
Who Changed Data
When It Changed
What Changedto audit tables.
Real-World Example: Banking
Requirement:
Log Every Balance ChangeTrigger stores:
Account Number
Old Balance
New Balance
Timestampfor auditing.
Real-World Example: HR System
Requirement:
Track Salary ModificationsTrigger records all changes automatically.
Real-World Example: E-Commerce
Requirement:
Update Inventory
After Order PlacementTrigger automatically reduces stock quantity.
Real-World Example: Education System
Requirement:
Track Student Record ChangesTriggers maintain audit history.
Trigger vs Stored Procedure
Stored Procedure:
Executed ManuallyTrigger:
Executed AutomaticallyComparison:
| Feature | Trigger | Stored Procedure |
|---|---|---|
| Automatic Execution | Yes | No |
| Event Driven | Yes | No |
| Manual Execution | No | Yes |
| Parameters | No | Yes |
| Business Logic | Limited | Extensive |
Trigger vs Function
Function:
Returns ValueTrigger:
Responds To EventsDifferent purposes.
Advantages of Triggers
Automation
Tasks execute automatically.
Data Integrity
Business rules enforced consistently.
Audit Logging
Changes tracked automatically.
Security
Unauthorized actions can be detected.
Reduced Application Complexity
Logic moved into database.
Disadvantages of Triggers
Hidden Logic
Developers may forget triggers exist.
Performance Overhead
Triggers add extra processing.
Difficult Debugging
Errors may be harder to locate.
Maintenance Challenges
Large trigger systems become complex.
Performance Considerations
Triggers execute automatically.
Heavy trigger logic can:
Increase CPU Usage
Slow Transactions
Affect PerformanceKeep triggers lightweight whenever possible.
Common Errors
Recursive Triggers
Trigger updates same table repeatedly.
May create infinite loops.
Heavy Processing
Complex logic reduces performance.
Missing Error Handling
Unexpected failures may occur.
Multiple Triggers Conflict
Several triggers may execute unexpectedly.
Best Practices
Keep Triggers Simple
Avoid large business workflows.
Use Triggers for Auditing
One of the best use cases.
Avoid Complex Calculations
Use procedures or application code instead.
Document Trigger Purpose
Prevent confusion.
Test Thoroughly
Automatic execution makes testing essential.
Common Interview Questions
What is a Trigger?
A database object that automatically executes when a specified event occurs.
What events can trigger execution?
INSERT
UPDATE
DELETEand sometimes DDL events.
What is the difference between a Trigger and a Stored Procedure?
Triggers execute automatically; procedures execute manually.
What are INSERTED and DELETED tables?
Temporary tables containing new and old row values.
Why are Triggers used?
For automation, auditing, validation, and maintaining data integrity.
Summary
Triggers are powerful event-driven database objects that automatically execute when data changes occur. They help automate auditing, enforce business rules, maintain integrity, and simplify application logic.
In this lesson, you learned:
- What Triggers are
- Why Triggers are important
- BEFORE Triggers
- AFTER Triggers
- INSTEAD OF Triggers
- DML Triggers
- DDL Triggers
- INSERT Triggers
- UPDATE Triggers
- DELETE Triggers
- INSERTED and DELETED tables
- Real-world applications
- Performance considerations
- Best practices
Mastering Triggers is essential because they are widely used in banking systems, ERP software, audit systems, HR platforms, and enterprise database applications.
Next Step
Continue to the next lesson:
Transactions in SQL →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Triggers in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, advanced, Triggers
Related SQL Topics