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.
Lets 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..
Instead of Trigger (using INSTEAD OF CLAUSE)
2,345 total views, 4 views today