DML Triggers with example-Part-1

DML Triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers.

Types of DML Triggers

  1. 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.

  2. 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.

 

Example

  1. — First create table Employee_Demo
  2. CREATE TABLE Employee_Demo
  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_Demo values (‘Amit’,1000);
  10. Insert into Employee_Demo values (‘Mohan’,1200);
  11. Insert into Employee_Demo values (‘Avin’,1100);
  12. Insert into Employee_Demo values (‘Manoj’,1300);
  13. Insert into Employee_Demo values (‘Riyaz’,1400);
  14. –Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo
  15. create table Employee_Demo_Audit
  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

)

Now I am going to explain the use of After Trigger using Insert, Update, Delete statement with example

1. After Insert Trigger

— Create trigger on table Employee_Demo for Insert statement
CREATE TRIGGER trgAfterInsert on Employee_Demo
FOR INSERT
AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action=’Inserted Record — After Insert Trigger.’;
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT ‘AFTER INSERT trigger fired.’

–Now try to insert data in Employee_Demo table
   insert into Employee_Demo(Emp_Name,Emp_Sal)values (‘Shailesh’,1000);
   –Output will be

  1. –now select data from both the tables to see trigger action
  2. select * from Employee_Demo
  3. select * from Employee_Demo_Audit
  4. –Output will be

image

image

Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can trace a insert activity on a table using trigger.

2. After Update Trigger

— Create trigger on table Employee_Demo for Update statement
CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo
FOR UPDATE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
if update(Emp_Name)
set @audit_action=’Update Record — After Update Trigger.’;
if update (Emp_Sal)
set @audit_action=’Update Record — After Update Trigger.’;
insert intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT ‘AFTER UPDATE trigger fired.’

–Now try to upadte data in Employee_Demo table
    update Employee_Demo set Emp_Name=’Pawan’ Where Emp_ID =6;
    –Output will be

–now select data from both the tables to see trigger action
    select * from Employee_Demo
    select * from Employee_Demo_Audit
    –Output will be

 

image

image

Trigger have inserted the new record to Employee_Demo_Audit table for update statement. In this way we can trace a update activity on a table using trigger.

3. After Delete Trigger

— Create trigger on table Employee_Demo for Delete statement
  CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
  FOR DELETE
  AS
  declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
  select @empid=d.Emp_ID FROM deleted d;
  select @empname=d.Emp_Name from deleted d;
  select @empsal=d.Emp_Sal from deleted d;
  select @audit_action=’Deleted — After Delete Trigger.’;
  insert into Employee_Demo_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
  values (@empid,@empname,@empsal,@audit_action,getdate());
  PRINT ‘AFTER DELETE TRIGGER fired.’
 

    –Now try to delete data in Employee_Demo table
    DELETE FROM Employee_Demo where emp_id = 5
    –Output will be

 

–now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
–Output will be

image

image

Trigger have inserted the new record to Employee_Demo_Audit table for delete statement. In this way we can trace a delete activity on a table using trigger.

I hope after reading this article your DML triggers concepts will be strong,I will explain the use of Instead of Trigger using Insert, Update, Delete in my next post.

Please post your feedback, question, or comments about this article.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s