AFTER UPDATE Trigger
Learn how AFTER UPDATE Triggers work in SQL Server, how to create them and how to use the inserted and deleted logical tables.
What is an AFTER UPDATE Trigger?
An AFTER UPDATE Trigger is a special SQL Server database object that automatically executes after a successful UPDATE operation on a table.
It is useful when we want to perform an automatic action whenever existing records are modified.
- It executes after an UPDATE operation.
- It can be used for auditing changes.
- It can store old and new values.
- It can maintain history or log tables.
- It can perform automatic database operations.
Basic Syntax
CREATE TRIGGER TriggerName
ON TableName
AFTER UPDATE
AS
BEGIN
-- Trigger logic
END;
Example Table
Let's assume we have a Students table.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100),
City VARCHAR(50),
Age INT
);
Insert Sample Data
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
101,
'Rahul',
'Pune',
21
);
Create an Audit Table
We can create a separate table to store information whenever a student record is updated.
CREATE TABLE StudentAudit
(
AuditId INT IDENTITY(1,1) PRIMARY KEY,
StudentId INT,
OldCity VARCHAR(50),
NewCity VARCHAR(50),
OldAge INT,
NewAge INT,
ChangedDate DATETIME
);
Creating an AFTER UPDATE Trigger
Now we create a trigger that automatically records the old and new values whenever a student is updated.
CREATE TRIGGER trg_StudentAfterUpdate
ON Students
AFTER UPDATE
AS
BEGIN
INSERT INTO StudentAudit
(
StudentId,
OldCity,
NewCity,
OldAge,
NewAge,
ChangedDate
)
SELECT
d.StudentId,
d.City,
i.City,
d.Age,
i.Age,
GETDATE()
FROM deleted d
INNER JOIN inserted i
ON d.StudentId = i.StudentId;
END;
inserted and deleted Logical Tables
SQL Server provides two special logical tables inside an UPDATE trigger:
| Logical Table | Contains |
|---|---|
| deleted | Old values before the UPDATE |
| inserted | New values after the UPDATE |
Performing an UPDATE
Now update the student's city and age.
UPDATE Students
SET
City = 'Mumbai',
Age = 22
WHERE StudentId = 101;
What Happens During UPDATE?
Suppose the old record was:
StudentId = 101
City = Pune
Age = 21
After the UPDATE:
StudentId = 101
City = Mumbai
Age = 22
Inside the trigger:
- deleted contains Pune and 21.
- inserted contains Mumbai and 22.
Check the Audit Record
SELECT *
FROM StudentAudit;
The audit table will contain the old and new values generated automatically by the trigger.
AFTER UPDATE Trigger and Multiple Rows
A trigger can execute for an UPDATE statement that modifies multiple rows.
UPDATE Students
SET City = 'Pune'
WHERE Age > 20;
Therefore, trigger logic should be written using set-based operations rather than assuming that only one row is updated.
Important
- AFTER UPDATE triggers execute after the UPDATE operation.
- deleted contains the old values.
- inserted contains the new values.
- A single UPDATE statement can affect multiple rows.
- Always design trigger logic to handle multiple rows.
Summary
An AFTER UPDATE Trigger automatically executes after a successful UPDATE operation. It is commonly used for auditing, maintaining history, tracking changes and performing automatic database operations. The deleted logical table contains the old values, while the inserted logical table contains the new values.