SQL DML AFTER TRIGGER

After Trigger (using FOR/AFTER CLAUSE) fires after SQL Server finishes the execution of the action successfully that fired it.

This type of trigger fires after SQL Server finishes the execution of the action successfully that fired it.

SYNTAX

CREATE TRIGGER triggername

ON { tablename }

AFTER {[INSERT] [,] [UPDATE] [,] [DELETE] }

AS

{ ---sqlstatements }

Lets look at an example of using After Triggers in SQL.

Create Table dbo.Employee(
EmpId Int identity(1,1), 
FirstName Varchar(50), 
LastName Varchar(50), 
City Varchar(50)
)

INSERT INTO dbo.Employee( FirstName, LastName, City) 
VALUES 
('Sumit', 'Agarwal', 'London'),
('Rohit', 'Singh', 'London'),
('Jay', 'Kumar', 'Redmond'),
('Amit', 'Kumar','Denmark')


Create Table dbo.Employee_Log(
EmpId Int, 
FirstName Varchar(50), 
LastName Varchar(50), 
City Varchar(50),
RecordStatus Varchar(15),
LogDate DateTime
)

Now we have two tables as shown below.

SELECT * FROM dbo.Employee
SELECT * FROM dbo.Employee_Log

Following statement uses After Triggers named trg_employee_log to keep the track of records when an insert and delete event occurs against the employee table.

CREATE TRIGGER trg_employee_log
ON dbo.Employee 
AFTER INSERT, DELETE 
AS 
BEGIN SET NOCOUNT ON; 
INSERT INTO dbo.Employee_Log (
EmpId, FirstName, LastName, City, RecordStatus, LogDate ) 
SELECT i.EmpId, FirstName, LastName, city, 'inserted' AS RecordStatus,
GETDATE() 
FROM inserted i 
UNION ALL
SELECT d.EmpId, FirstName, LastName, city, 'deleted' AS RecordStatus,
GETDATE() 
FROM deleted d;
END

You can see trigger has been created on table, lets quickly check the trigger for Insert event by inserting a new record into table employee.

INSERT INTO dbo.Employee( FirstName, LastName, City) 
VALUES ('Anthony', 'Mark','Denmark')

As you have inserted a new records into table Employee, a After trigger that you have created on table Employee gets fire in response of insert event and insert a same details into Employee_log table.

Also, you can see the value in RecordStatus column that is ‘inserted’.

Lets quickly check a both tables as shown below.

SELECT * FROM dbo.Employee
SELECT * FROM dbo.Employee_Log

As you can see, when a new record is inserted in employee table, an after trigger gets fire in response of insert event and insert a same record entry in employee_log table.

Lets check the trigger for delete event by deleting a record from table.

Now, delete a record for Empid =1 from employee table.

DELETE FROM dbo.Employee 
WHERE EmpId = 1

Lets check whether the trigger inserts a delete log into table employee_log or not.

SELECT * FROM dbo.Employee
SELECT * FROM dbo.Employee_Log

As you can see, when record for EmpId =1 is deleted from employee table, a After trigger gets fire in response of delete event and insert a deleted record entry in employee_log table.

So far we have seen, how to track inserted and deleted records using After trigger.

Lets modify this trigger for tracking the modified/updated records as well in a table.

ALTER TRIGGER trg_employee_log 
ON dbo.Employee AFTER INSERT, UPDATE, DELETE 
AS 
BEGIN 
SET NOCOUNT ON; 
DECLARE @deletedcnt INT, @insertedcnt INT 
SELECT @deletedcnt = COUNT(1) FROM deleted 
SELECT @insertedcnt = COUNT(1) FROM inserted 
IF(@deletedcnt & @insertedcnt > 0) 
BEGIN INSERT INTO Employee_Log( EmpId, FirstName, LastName, City, 
RecordStatus, LogDate ) 
SELECT i.EmpId, FirstName, LastName, City, 'updated' AS RecordStatus, 
GETDATE() 
FROM inserted i
END 
ELSE 
IF(@deletedcnt >0) 
BEGIN 
INSERT INTO Employee_Log( EmpId, FirstName, LastName, City, 
RecordStatus, LogDate ) 
SELECT d.EmpId, FirstName, LastName, City,'deleted' AS RecordStatus, 
GETDATE() 
FROM deleted d; 
END 
ELSE 
BEGIN 
INSERT INTO Employee_Log( EmpId, FirstName, LastName, City, 
RecordStatus, LogDate ) 
SELECT i.EmpId, FirstName, LastName, City, 'inserted' AS RecordStatus, 
GETDATE() 
FROM inserted i 
END 
END

Lets update a record in employee table to see the implementation of After trigger for update event.

UPDATE dbo.Employee SET LastName ='Rawat' 
WHERE EmpId = 2

Lets see the in table, and you can see a updated record entry is inserted into employee_log table.

SELECT * FROM dbo.Employee
SELECT * FROM dbo.Employee_Log

 

 

As you can see, when record is updated in employee table, an after trigger gets fire in response of update event and insert a updated record entry in employee_log table.

Also Read..

DDL Triggers

LOGON Trigger

Instead of Trigger (using INSTEAD OF CLAUSE)