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.

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

CREATE TRIGGER trg_Employees_track
ON Employees
AFTER INSERT ,DELETE
AS
BEGIN
SET NOCOUNT ON; 
INSERT INTO emplpyee_track( EmpId, LastName, FirstName, city, record_status, Date 
)
SELECT i.EmployeeId, LastName, FirstName, city, 'inserted' as reocrd_status , GETDATE()
FROM
inserted i

UNION ALL

SELECT d.EmployeeId, LastName, FirstName, city, 'deleted' as reocrd_status , GETDATE()
FROM
deleted d;
END

 

Lets quickly check the trigger for Insert event by inserting a record in employees table.

Insert into Employees ( LastName, FirstName, city) values
('Anthony' ,'Mark','Denmark')

select EmployeeId, LastName, FirstName, city from Employees
Select * from emplpyee_track

 

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 emplpyee_track table.

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

delete from Employees where EmployeeID =1011

 

select EmployeeId, LastName, FirstName, city from Employees
Select * from emplpyee_track

 

 

As you can see, when record is deleted from employee table, a after trigger gets fire in response of delete event and insert a deleted record entry in emplpyee_track table.

Lets modify this trigger for tracking the modified records in a table

ALTER TRIGGER trg_Employees_track
ON Employees 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 emplpyee_track( EmpId, LastName, FirstName, city, record_status, Date )
SELECT i.EmployeeId, LastName, FirstName, city, 'updated' as reocrd_status, GETDATE() 
FROM inserted i

END

ELSE IF (@deletedcnt >0)

BEGIN
INSERT INTO emplpyee_track( EmpId, LastName, FirstName, city, record_status, Date 
)
SELECT d.EmployeeId, LastName, FirstName, city,'deleted' as reocrd_status, GETDATE() 
FROM deleted d;
END

ELSE 
BEGIN
INSERT INTO emplpyee_track( EmpId, LastName, FirstName, city, record_status, Date )
SELECT i.EmployeeId, LastName, FirstName, city, 'inserted' as reocrd_status, GETDATE() 
FROM inserted i
END 
END

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


Update employees set lastName ='Anthony S' where EmployeeId =8

select EmployeeId, LastName, FirstName, city from Employees
Select * from emplpyee_track

 

 

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 emplpyee_track table.

Recommended for you

DDL Triggers

LOGON Trigger

Instead of Trigger (using INSTEAD OF CLAUSE)




 35 total views,  2 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.