Triggers in MS SQL
Types of DML Triggers
After Trigger (using FOR/AFTER CLAUSE)
This trigger fires after SQL Server completes the execution of the action successfully that fired it.
Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.
Instead of Trigger (using INSTEAD OF CLAUSE)
This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.
Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
Now we will develop one example of each type of triggers.
Example
First of all create two tables Employee and Employee_Backup,Insert record in it as shown below:
1. -- First create table Employee
2. CREATE TABLE Employee
3. (
4. Emp_ID int identity,
5. Emp_Name varchar(55),
6. Emp_Sal decimal (10,2)
7. )
8. -- Now Insert records
9. Insert into Employee values ('Amit',1000);
10. Insert into Employee values ('Mohan',1200);
11. Insert into Employee values ('Avin',1100);
12. Insert into Employee values ('Manoj',1300);
13. Insert into Employee values ('Riyaz',1400);
14. --Now create table Employee_Backup for logging/backup purpose of table Employee create
table Employee_Backup
15. CREATE TABLE Employee_Backup
16. (
17. Emp_ID int,
18. Emp_Name varchar(55),
19. Emp_Sal decimal(10,2),
20. Audit_Action varchar(100),
21. Audit_Timestamp datetime
22. )
Now I am going to explain the use of After Trigger using Insert, Update, Delete statement with example.
First we prepare after trigger for insert statement.
After Insert Trigger
1. -- Create trigger on table Employee for Insert statement
2. CREATE TRIGGER trgAfterInsert on Employee
3. FOR INSERT AS declare
4. @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
5. select @empid=i.Emp_ID from inserted i;
6. select @empname=i.Emp_Name from inserted i;
7. select @empsal=i.Emp_Sal from inserted i;
8. set @audit_action='Inserted Record -- After Insert Trigger.';
9. insert into Employee_Backup(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_
10. Timestamp) values (@empid,@empname,@empsal,@audit_action,getdate());
11. PRINT 'AFTER INSERT trigger fired.'
Run this command and trigger will be created and you will get output shown below.
--Now try to insert data in Employee table
1. insert into Employee(Emp_Name,Emp_Sal)values ('Shailu',1000);
Now select data from both the tables to see trigger action
1. select * from Employee;
2. select * from Employee_Backup;
Output will be
Trigger have inserted the new record to Employee_Backup table for insert statement. In this way we can trace a insert activity on a table using trigger.
After Update Trigger
1. -- Create trigger on table Employee for Update statement
2. CREATE TRIGGER trgAfterUpdate ON dbo.Employee
3. FOR UPDATE
4. AS declare
5. @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
6. select @empid=i.Emp_ID from inserted i;
7. select @empname=i.Emp_Name from inserted i;
8. select @empsal=i.Emp_Sal from inserted i; if update(Emp_Name)
9. set @audit_action='Update Record --- After Update Trigger.';
10. if update (Emp_Sal)
11. set @audit_action='Update Record --- After Update Trigger.';
12. insert intoEmployee_Backup(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_
13. Timestamp) values (@empid,@empname,@empsal,@audit_action,getdate());
14. PRINT 'AFTER UPDATE trigger fired.'
Run this command and trigger will be created and you will get output shown below.
--Now try to update data in Employee table
1. update Employee set Emp_Name='Pawan' Where Emp_ID =6;
Now select data from both the tables to see trigger action
1. select * from Employee;
2. select * from Employee_Backup;
Output will be
Trigger have inserted the new record to Employee_Backup table for update statement. In this way we can trace a update activity on a table using trigger.
After Delete Trigger
1. -- Create trigger on table Employee for Delete statement
2. CREATE TRIGGER trgAfterDelete ON dbo.Employee
3. FOR DELETE
4. AS declare
5. @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
6. select @empid=d.Emp_ID FROM deleted d;
7. select @empname=d.Emp_Name from deleted d;
8. select @empsal=d.Emp_Sal from deleted d;
9. select @audit_action='Deleted -- After Delete Trigger.';
10. insert into Employee_Backup (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_
11. Timestamp) values (@empid,@empname,@empsal,@audit_action,getdate());
12. PRINT 'AFTER DELETE TRIGGER fired.'
Run this command and trigger will be created and you will get output shown below.
--Now try to delete data in Employee table
1. DELETE FROM Employee where emp_id = 5;
Now select data from both the tables to see trigger action
1. select * from Employee;
2. select * from Employee_Backup;
Output will be
Trigger have inserted the new record to Employee_Backup table for delete statement. In this way we can trace a delete activity on a table using trigger.
Now I am going to explain the use of Instead of Trigger using Insert, Update, Delete statement with example
Instead of Insert Trigger
1. -- Create trigger on table Employee for Insert statement
2. CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee
3. INSTEAD OF Insert
4. AS declare
5. @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
6. select @emp_id=i.Emp_ID from inserted i;
7. select @emp_name=i.Emp_Name from inserted i;
8. select @emp_sal=i.Emp_Sal from inserted i;
9. SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
10. BEGIN
11. BEGIN TRAN
12. SET NOCOUNT ON
13. if(@emp_sal>=1000)
14. begin
15. RAISERROR('Cannot Insert where salary < 1000',16,1);
16. ROLLBACK;
17. end
18. else begin
19. Insert into Employee (Emp_Name,Emp_Sal) values (@emp_name,@emp_sal);
20. Insert into Employee_Backup(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_
21. Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate())
22. COMMIT;
23. PRINT 'Record Inserted -- Instead Of Insert Trigger.'
24. END;
Run this command and trigger will be created and you will get output shown below.
--Now try to insert data in Employee table
1. insert into Employee values ('Shailu',1300);
Now select data from both the tables to see trigger action
1. select * from Employee;
2. select * from Employee_Backup;
Output will be
Trigger have inserted the new record to Employee_Backup table for insert statement. In this way we can apply business validation on the data to be inserted using Instead of trigger and can also trace insert activity on a table.
Instead of Update Trigger
1. -- Create trigger on table Employee for Update statement
2. CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee
3. INSTEAD OF Update
4. AS declare
5. @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
6. select @emp_id=i.Emp_ID from inserted i;
7. select @emp_name=i.Emp_Name from inserted i;
8. select @emp_sal=i.Emp_Sal from inserted i;
9. BEGIN
10. BEGIN TRAN
11. if(@emp_sal>=1000)
12. begin
14. RAISERROR('Cannot Insert where salary < 1000',16,1);
15. ROLLBACK;
16. end
17. else begin
18. insert into Employee_Backup(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_
19. Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());
20. COMMIT;
21. PRINT 'Record Updated -- Instead Of Update Trigger.';
22. END;
Run this command and trigger will be created and you will get output shown below.
--Now try to update data in Employee table
1. update Employee set Emp_Sal = '1400' where emp_id = 6;
2. update Employee set Emp_Sal = '900' where emp_id = 6;
Now select data from both the tables to see trigger action
1. select * from Employee;
2. select * from Employee_Backup;
Output will be
Trigger have inserted the updated record to Employee_Backup table for update statement. In this way we can apply business validation on the data to be updated using Instead of trigger and can also trace a update activity on a table.
Instead of Delete Trigger
1. -- Create trigger on table Employee for Delete statement
2. CREATE TRIGGER trgAfterDelete ON dbo.Employee
3. INSTEAD OF DELETE
4. AS declare
5. @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
6. select @empid=d.Emp_ID FROM deleted d;
7. select @empname=d.Emp_Name from deleted d;
8. select @empsal=d.Emp_Sal from deleted d;
9. BEGIN TRAN if(@empsal>1200) begin
10. RAISERROR('Cannot delete where salary > 1200',16,1);
11. ROLLBACK;
12. end
13. else begin
14. delete from Employee where Emp_ID=@empid;
15. COMMIT;
16. insert into Employee_Backup(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_
17. Timestamp) values(@empid,@empname,@empsal,@audit_action,getdate());
18. PRINT 'Record Deleted -- Instead Of Delete Trigger.'
19. END;
Run this command and trigger will be created and you will get output shown below.
--Now try to delete data in Employee table
1. DELETE FROM Employee where emp_id = 1;
2. DELETE FROM Employee where emp_id = 3;
Now select data from both the tables to see trigger action
1. select * from Employee;
2. select * from Employee_Backup;
Trigger have inserted the deleted record to Employee_Backup table for delete statement. In this way we can apply business validation on the data to be deleted using Instead of trigger and can also trace a delete activity on a table.
I hope this article will help you.