SQL Topics
AFTER Trigger
title: AFTER Trigger
In the previous lesson, you learned about BEFORE Triggers and how they execute before a database operation occurs.
BEFORE Triggers are commonly used for:
Validation
Data Integrity
Business Rule EnforcementHowever, many situations require actions to occur after a database operation has completed successfully.
Examples:
Create Audit Log
Send Notification
Update Statistics
Record Transaction HistoryTo perform such tasks, SQL provides:
AFTER TriggerAn AFTER Trigger executes only after the database operation has successfully completed.
What is an AFTER Trigger?
An AFTER Trigger is a trigger that automatically executes after an INSERT, UPDATE, or DELETE operation completes successfully.
Think of it as:
Database Event
↓
Operation Completes
↓
AFTER Trigger Executes
↓
Additional Action PerformedUnlike BEFORE Triggers, AFTER Triggers are generally used for:
Logging
Auditing
Tracking Changes
AutomationSimple Definition
An AFTER Trigger is a database trigger that executes automatically after a specified database event successfully completes.
Why Are AFTER Triggers Important?
Suppose a company wants to record every salary change.
Whenever:
UPDATE Employeesoccurs, management wants to store:
Employee ID
Old Salary
New Salary
Update TimeWithout AFTER Triggers:
Application Must Perform LoggingWith AFTER Triggers:
Database Automatically Logs ChangesReal-Life Analogy
Imagine online shopping.
Process:
Place Order
↓
Order Saved
↓
Confirmation Email SentThe email is sent only after the order is successfully stored.
Similarly:
Database Operation
↓
Success
↓
AFTER Trigger ExecutesHow AFTER Triggers Work
Workflow:
INSERT/UPDATE/DELETE
↓
Operation Completes
↓
AFTER Trigger Fires
↓
Additional SQL ExecutesTrigger Execution Flow
Example:
INSERT INTO Employees
VALUES (...);Process:
Events Supported by AFTER Triggers
AFTER Triggers can be created for:
INSERT
UPDATE
DELETEevents.
AFTER INSERT Trigger
Executes after:
INSERToperations.
Common uses:
Create Log Record
Send Notification
Update StatisticsAFTER UPDATE Trigger
Executes after:
UPDATEoperations.
Common uses:
Track Changes
Store History
Audit UpdatesAFTER DELETE Trigger
Executes after:
DELETEoperations.
Common uses:
Archive Deleted Data
Audit Deletions
Track ActivityBasic AFTER Trigger Syntax
MySQL Example:
CREATE TRIGGER TriggerName
AFTER INSERT
ON TableName
FOR EACH ROW
BEGIN
SQL Statements;
END;Understanding the Syntax
CREATE TRIGGERCreates the trigger.
AFTER INSERTSpecifies timing and event.
ON TableNameDefines target table.
FOR EACH ROWRuns for every affected row.
BEGIN
...
ENDContains trigger logic.
Creating Sample Tables
Employees Table:
CREATE TABLE Employees
(
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Audit Table:
CREATE TABLE EmployeeAudit
(
AuditID INT AUTO_INCREMENT,
EmployeeID INT,
ActionType VARCHAR(20),
ActionDate DATETIME
);AFTER INSERT Example
Requirement:
Log Every New EmployeeTrigger:
CREATE TRIGGER LogEmployeeInsert
AFTER INSERT
ON Employees
FOR EACH ROW
BEGIN
INSERT INTO EmployeeAudit
(
EmployeeID,
ActionType,
ActionDate
)
VALUES
(
NEW.EmployeeID,
'INSERT',
NOW()
);
END;How It Works
User executes:
INSERT INTO Employees
VALUES
(
1,
'Rahul',
50000
);Database:
Employee Added
↓
Trigger Fires
↓
Audit Record AddedAFTER UPDATE Example
Requirement:
Track Salary UpdatesTrigger:
CREATE TRIGGER LogSalaryUpdate
AFTER UPDATE
ON Employees
FOR EACH ROW
BEGIN
INSERT INTO EmployeeAudit
(
EmployeeID,
ActionType,
ActionDate
)
VALUES
(
NEW.EmployeeID,
'UPDATE',
NOW()
);
END;Execution Example
UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 1;Result:
Employee Updated
Audit Entry CreatedAFTER DELETE Example
Requirement:
Track Employee DeletionsTrigger:
CREATE TRIGGER LogEmployeeDelete
AFTER DELETE
ON Employees
FOR EACH ROW
BEGIN
INSERT INTO EmployeeAudit
(
EmployeeID,
ActionType,
ActionDate
)
VALUES
(
OLD.EmployeeID,
'DELETE',
NOW()
);
END;Understanding OLD and NEW
AFTER Triggers commonly use:
OLD
NEWkeywords.
NEW
Represents:
New Row Valuesafter INSERT or UPDATE.
Example:
NEW.SalaryOLD
Represents:
Previous Row Valuesbefore UPDATE or DELETE.
Example:
OLD.SalarySalary Change Audit Example
Audit Table:
CREATE TABLE SalaryAudit
(
EmployeeID INT,
OldSalary DECIMAL(10,2),
NewSalary DECIMAL(10,2),
ChangeDate DATETIME
);Trigger:
CREATE TRIGGER SalaryHistory
AFTER UPDATE
ON Employees
FOR EACH ROW
BEGIN
INSERT INTO SalaryAudit
VALUES
(
NEW.EmployeeID,
OLD.Salary,
NEW.Salary,
NOW()
);
END;Real-World Example: Banking
Requirement:
Track Every Money TransferTrigger automatically creates:
Transaction Logafter successful transfers.
Real-World Example: E-Commerce
Requirement:
Update Sales Statisticsafter order creation.
AFTER INSERT Trigger handles this automatically.
Real-World Example: Hospital System
Requirement:
Track Patient Record Updatesfor compliance and auditing.
Real-World Example: University System
Requirement:
Log Student Registrationsafter enrollment.
Advantages of AFTER Triggers
Automatic Execution
No manual action required.
Excellent Audit Logging
Tracks all changes automatically.
Improved Accountability
Maintains activity history.
Centralized Logic
Database manages automation.
Better Monitoring
Useful for compliance systems.
Disadvantages of AFTER Triggers
Performance Overhead
Extra SQL executes after each operation.
Hidden Processing
Developers may not know triggers exist.
Debugging Complexity
Errors may originate from trigger logic.
Excessive Logging
Large audit tables may grow rapidly.
Common Mistakes
Logging Too Much Data
Creates large audit tables.
Complex Trigger Logic
Reduces performance.
Infinite Trigger Chains
One trigger firing another repeatedly.
Poor Documentation
Makes maintenance difficult.
Best Practices
Keep Triggers Lightweight
Focus on essential tasks.
Use Dedicated Audit Tables
Separate operational and audit data.
Document Trigger Behavior
Improve maintainability.
Monitor Storage Growth
Audit tables can grow quickly.
Test Thoroughly
Verify all trigger actions.
BEFORE Trigger vs AFTER Trigger
| Feature | BEFORE Trigger | AFTER Trigger |
|---|---|---|
| Executes Before Event | Yes | No |
| Executes After Event | No | Yes |
| Validation | Excellent | Limited |
| Audit Logging | Possible | Excellent |
| Prevent Operation | Yes | Usually No |
| Change Tracking | Limited | Excellent |
Common Interview Questions
What is an AFTER Trigger?
A trigger that executes after a database event completes successfully.
Why are AFTER Triggers used?
For logging, auditing, notifications, and automation.
Can an AFTER Trigger stop an INSERT?
Normally no, because the operation has already completed.
What is NEW in an AFTER Trigger?
Represents the new row values.
What is OLD in an AFTER Trigger?
Represents the previous row values.
Summary
AFTER Triggers execute automatically after INSERT, UPDATE, or DELETE operations complete successfully. They are commonly used for audit logging, change tracking, notifications, automation, and maintaining historical records.
In this lesson, you learned:
- What AFTER Triggers are
- How AFTER Triggers work
- AFTER INSERT
- AFTER UPDATE
- AFTER DELETE
- OLD and NEW keywords
- Audit logging examples
- Real-world applications
- Advantages and disadvantages
- Best practices
Mastering AFTER Triggers is essential because they are one of the most widely used mechanisms for auditing and automation in enterprise database systems.
Next Step
Continue to the next lesson:
INSTEAD OF Trigger →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for AFTER Trigger.
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, after
Related SQL Topics