AFTER INSERT Trigger
Learn how to create and use an AFTER INSERT Trigger in SQL Server.
What is an AFTER INSERT Trigger?
An AFTER INSERT Trigger is a special type of SQL Server trigger that automatically executes after a new record is successfully inserted into a table.
It is commonly used for auditing, logging, maintaining related data and performing automatic actions after an INSERT operation.
How AFTER INSERT Trigger Works
When an INSERT statement is executed, SQL Server first inserts the new record into the table.
After the INSERT operation is completed successfully, the AFTER INSERT Trigger automatically executes.
INSERT Operation → Record Inserted → AFTER INSERT Trigger Executes
The INSERTED Table
SQL Server provides a special logical table called inserted inside an INSERT Trigger.
The inserted table contains the newly inserted records.
Example: Students Table
Let's create a simple Students table for understanding the AFTER INSERT Trigger.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100),
City VARCHAR(50),
Age INT
);
Create an Audit Table
We can create another table to store information about newly inserted students.
CREATE TABLE StudentAudit
(
AuditId INT IDENTITY(1,1) PRIMARY KEY,
StudentId INT,
StudentName VARCHAR(100),
ActionDate DATETIME
);
Creating an AFTER INSERT Trigger
Now we will create a trigger that automatically stores information in the StudentAudit table whenever a new student is inserted.
CREATE TRIGGER trg_AfterInsertStudent
ON Students
AFTER INSERT
AS
BEGIN
INSERT INTO StudentAudit
(
StudentId,
StudentName,
ActionDate
)
SELECT
StudentId,
StudentName,
GETDATE()
FROM inserted;
END;
Explanation
| Statement | Purpose |
|---|---|
CREATE TRIGGER |
Creates a new trigger. |
ON Students |
Specifies the table on which the trigger is created. |
AFTER INSERT |
Specifies that the trigger executes after an INSERT operation. |
inserted |
Contains the newly inserted records. |
GETDATE() |
Returns the current date and time. |
Insert a New Student
Now insert a new student into the Students table.
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
101,
'Rahul',
'Pune',
22
);
The student is inserted into the Students table.
After the INSERT operation, trg_AfterInsertStudent automatically executes.
The student's details are then stored in the StudentAudit table.
Check the Audit Table
Execute the following query to check the data inserted by the trigger.
SELECT *
FROM StudentAudit;
AFTER INSERT Trigger with Multiple Rows
The inserted table can contain multiple rows when multiple records are inserted at once.
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(102, 'Priya', 'Mumbai', 23),
(103, 'Amit', 'Pune', 24),
(104, 'Sneha', 'Nashik', 22);
The trigger executes once for the INSERT statement and processes the rows available in the inserted table.
Important Points
- AFTER INSERT Trigger executes after an INSERT operation.
- It is automatically executed by SQL Server.
- The inserted logical table contains newly inserted rows.
- A trigger can be used for auditing and logging.
- A trigger should be designed carefully because it executes automatically.
- Triggers can process multiple inserted rows.
AFTER INSERT Trigger Syntax
CREATE TRIGGER TriggerName
ON TableName
AFTER INSERT
AS
BEGIN
-- Trigger statements
END;
Summary
An AFTER INSERT Trigger automatically executes after a successful INSERT operation.
It is useful for auditing, logging and performing automatic operations after new records are inserted.
The inserted logical table provides access to the newly inserted records inside the trigger.