SQL Topics
Trigger Introduction
title: Trigger Introduction
In the previous module, you learned about:
Stored Procedures
Procedure Parameters
Procedure Execution
Procedure vs FunctionStored Procedures execute only when they are explicitly called.
However, many database operations need to happen automatically whenever specific events occur.
For example:
Log Every Employee Update
Track Product Price Changes
Record Deleted Records
Audit Banking TransactionsManually executing procedures for these tasks is not practical.
To automate such actions, SQL provides:
TriggersTriggers automatically execute when specific database events occur.
They are one of the most powerful automation features available in relational database systems.
What is a Trigger?
A Trigger is a special database object that automatically executes in response to a specific database event.
Think of a Trigger as:
Automatic Procedurethat runs whenever:
INSERT
UPDATE
DELETEoperations occur.
Unlike stored procedures:
No Manual Execution RequiredThe database automatically fires the trigger when the specified event happens.
Simple Definition
A Trigger is a stored set of SQL statements that automatically executes when a specified database event occurs.
Why Are Triggers Important?
Suppose an employee's salary changes.
Management wants:
Store Old Salary
Store New Salary
Store Change Date
Store User Informationevery time an update occurs.
Without triggers:
Application Must Handle Loggingwhich increases complexity.
With triggers:
Database Automatically Logs ChangesReal-Life Analogy
Imagine:
Motion SensorWhen motion occurs:
Light Turns On AutomaticallySimilarly:
Database Event
↓
Trigger Fires
↓
Automatic ActionHow Triggers Work
Process:
Database Event
↓
Trigger Activated
↓
Trigger Executes SQL
↓
Action CompletedTrigger Workflow
Example:
INSERT New EmployeeDatabase detects:
INSERT EventTrigger executes:
Create Audit RecordAutomatically.
Events That Can Fire Triggers
Most SQL databases support:
INSERT
UPDATE
DELETEevents.
INSERT Trigger
Executes when:
New Record AddedExample:
INSERT INTO Employees
VALUES (...);Trigger fires automatically.
UPDATE Trigger
Executes when:
Existing Record ModifiedExample:
UPDATE Employees
SET Salary = 70000;Trigger fires automatically.
DELETE Trigger
Executes when:
Record RemovedExample:
DELETE FROM Employees;Trigger executes.
Why Use Triggers?
Triggers help automate tasks such as:
Audit Logging
Data Validation
Security Enforcement
Automatic Calculations
Business Rule EnforcementCommon Trigger Applications
Triggers are widely used for:
Audit Systems
Banking Applications
Inventory Systems
Payroll Systems
Hospital ManagementExample: Audit Logging
Suppose salary changes occur.
Whenever:
UPDATE Employeeshappens,
a trigger automatically stores:
Old Salary
New Salary
Date
Userin an audit table.
Example: Inventory Management
When an order is created:
INSERT INTO OrdersTrigger automatically:
Reduce Inventory QuantityExample: Banking System
Whenever money is transferred:
Transaction Recordcan be automatically generated.
Example: Hospital System
When a patient record changes:
Audit Trail Createdautomatically.
Trigger vs Stored Procedure
Many beginners confuse Triggers and Stored Procedures.
Stored Procedure
Manual Execution RequiredExample:
EXEC CalculateSalary;Trigger
Automatic ExecutionExample:
UPDATE Employees
SET Salary = 70000;Trigger executes automatically.
Comparison Table
| Feature | Trigger | Stored Procedure |
|---|---|---|
| Automatic Execution | Yes | No |
| Manual Execution | No | Yes |
| Event Based | Yes | No |
| Stored In Database | Yes | Yes |
| Business Logic | Yes | Yes |
| Audit Logging | Excellent | Limited |
| Automation | Excellent | Moderate |
Trigger vs Function
Function:
Returns ValueTrigger:
Performs Actionautomatically.
Types of Triggers
Most database systems support:
BEFORE Trigger
AFTER Trigger
INSTEAD OF TriggerThese will be covered in separate lessons.
BEFORE Trigger
Executes:
Before Event OccursExample:
Validate Data
Before InsertAFTER Trigger
Executes:
After Event CompletesExample:
Create Audit Record
After UpdateINSTEAD OF Trigger
Executes:
Instead Of Original ActionCommonly used with views.
Trigger Components
A Trigger generally contains:
Trigger Name
Trigger Event
Trigger Timing
SQL StatementsGeneral Trigger Structure
CREATE TRIGGER TriggerName
Trigger Timing
Trigger Event
ON TableName
FOR EACH ROW
BEGIN
SQL Statements
END;Different databases use slightly different syntax.
Advantages of Triggers
Automatic Execution
No manual calls required.
Improved Data Integrity
Enforces business rules.
Audit Logging
Tracks database changes.
Security Enforcement
Monitors unauthorized activity.
Centralized Logic
Rules remain inside the database.
Disadvantages of Triggers
Hidden Execution
Triggers execute automatically.
Developers may forget they exist.
Performance Overhead
Too many triggers can slow operations.
Debugging Difficulty
Automatic execution can complicate troubleshooting.
Maintenance Challenges
Complex trigger systems become difficult to manage.
Real-World Example: Employee Audit
Whenever salary changes:
Store Old Salary
Store New Salary
Store Timestampautomatically.
Real-World Example: E-Commerce
When an order is placed:
Reduce Stock
Generate Invoice
Update Analyticsautomatically.
Real-World Example: Banking
When money transfers occur:
Create Audit Record
Update Balance
Generate Log Entryautomatically.
Real-World Example: University System
When students register:
Update Enrollment Count
Generate Registration Logautomatically.
Common Mistakes
Creating Too Many Triggers
Can reduce performance.
Infinite Trigger Loops
Trigger updates same table repeatedly.
Poor Documentation
Developers forget trigger behavior.
Heavy Business Logic Inside Triggers
Can slow transactions.
Best Practices
Keep Triggers Small
Perform focused tasks.
Document Trigger Purpose
Improve maintainability.
Avoid Complex Logic
Keep execution efficient.
Test Thoroughly
Verify expected behavior.
Monitor Performance
Check trigger impact.
Common Interview Questions
What is a Trigger?
A database object that automatically executes when a specified event occurs.
Why are Triggers used?
For automation, auditing, validation, and business rule enforcement.
What events can fire Triggers?
INSERT
UPDATE
DELETEWhat is the difference between a Trigger and a Stored Procedure?
Triggers execute automatically, while procedures require manual execution.
Are Triggers useful for auditing?
Yes.
Audit logging is one of the most common trigger use cases.
Summary
Triggers are automatic database programs that execute when specific events occur. They are widely used for auditing, automation, validation, security, and enforcing business rules in enterprise applications.
In this lesson, you learned:
- What Triggers are
- Why Triggers are important
- How Triggers work
- Trigger events
- Trigger types overview
- Trigger vs Stored Procedure
- Trigger vs Function
- Real-world examples
- Advantages and disadvantages
- Best practices
Mastering Triggers is essential because they help automate database operations and maintain data integrity without requiring manual intervention.
Next Step
Continue to the next lesson:
BEFORE Trigger →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Trigger Introduction.
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, triggers, trigger
Related SQL Topics