Home » SQL DML AFTER TRIGGER

SQL DML AFTER TRIGGER

After Trigger (using FOR/AFTER CLAUSE)


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 }


Following statement uses  After triggers 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.

DDL Triggers

LOGON Trigger

Instead of Trigger (using INSTEAD OF CLAUSE)

 

Leave a Reply

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