Your browser does not support JavaScript! Please enable script of your browser.
 
Coding is Rhyme
Handy Tricks For Beginners & Professionals
SQL Server 2012, 2008 - Tutorial, Article, Archive, Pdf, Handy Tricks, Training, Code Snippets, Reference Manual, T-SQL, PL/SQL, Development, Administration, Performance Tuning, Backup, Recovery, Reporting, Analysis, Security, XML, CLR Objects

After Trigger, Instead of Trigger Example

Posted By : Shailendra Chauhan, 09 May 2011
Updated On : 24 Jun 2014
  Version Support : SQL Server 2005,2008,2012
Keywords : DML Triggers Example, Sql Server Triggers Example pdf, Instead of Trigger Example, After Trigger Example

Triggers are special type of stored procedure that automatically execute when a DDL or DML statement associated with the trigger is executed. 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/delet 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

 -- First create table Employee_Demo
CREATE TABLE Employee_Demo
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Sal decimal (10,2)
)
-- Now Insert records 
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);
Insert into Employee_Demo values ('Riyaz',1400);
--Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo create table Employee_Demo_Audit
(
 Emp_ID int,
 Emp_Name varchar(55),
 Emp_Sal decimal(10,2),
 Audit_Action varchar(100),
 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.'
    --Output will be 
     --Now try to insert data in Employee_Demo table
    insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);
    --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 

    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.'
    --Output will be 
     --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 

    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.'
    --Output will be 
     --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 

    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.

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

  1. Instead of Insert Trigger

     -- Create trigger on table Employee_Demo for Insert statement
    CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo
    INSTEAD OF Insert
    AS
    declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
    select @emp_id=i.Emp_ID from inserted i;
    select @emp_name=i.Emp_Name from inserted i;
    select @emp_sal=i.Emp_Sal from inserted i;
    SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
    BEGIN 
     BEGIN TRAN
     SET NOCOUNT ON
     if(@emp_sal>=1000)
     begin
     RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
     else begin Insert into Employee_Demo (Emp_Name,Emp_Sal) values (@emp_name,@emp_sal); Insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@@identity,@emp_name,@emp_sal,@audit_action,getdate());
     COMMIT;
     PRINT 'Record Inserted -- Instead Of Insert Trigger.'
    END
    --Output will be 
     --Now try to insert data in Employee_Demo table
    insert into Employee_Demo values ('Shailu',1300)
    insert into Employee_Demo values ('Shailu',900) -- It will raise error since we are checking salary >=1000
    --Outputs 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 

    Trigger have inserted the new record to Employee_Demo_Audit 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 a insert activity on a table.

  2. Instead of Update Trigger

     -- Create trigger on table Employee_Demo for Update statement
    CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo
    INSTEAD OF Update
    AS
    declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
    select @emp_id=i.Emp_ID from inserted i;
    select @emp_name=i.Emp_Name from inserted i;
    select @emp_sal=i.Emp_Sal from inserted i;
    BEGIN
     BEGIN TRAN
    if(@emp_sal>=1000)
     begin
     RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
     else begin 
     insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());
     COMMIT;
     PRINT 'Record Updated -- Instead Of Update Trigger.'; END
    --Output will be 
     --Now try to upadte data in Employee_Demo table
    update Employee_Demo set Emp_Sal = '1400' where emp_id = 6
    update Employee_Demo set Emp_Sal = '900' 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 

    Trigger have inserted the updated record to Employee_Demo_Audit 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.

  3. Instead of Delete Trigger

     -- Create trigger on table Employee_Demo for Delete statement
    CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
    INSTEAD OF 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;
    BEGIN TRAN if(@empsal>1200) begin
     RAISERROR('Cannot delete where salary > 1200',16,1);
     ROLLBACK;
     end
     else begin
     delete from Employee_Demo where Emp_ID=@empid;
     COMMIT;
     insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
     values(@empid,@empname,@empsal,'Deleted -- Instead Of Delete Trigger.',getdate());
     PRINT 'Record Deleted -- Instead Of Delete Trigger.' end END
    --Output will be 
     --Now try to delete data in Employee_Demo table
    DELETE FROM Employee_Demo where emp_id = 1
    DELETE FROM Employee_Demo where emp_id = 3
    --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 

    Trigger have inserted the deleted record to Employee_Demo_Audit 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.

What do you think?

In this article I try to explain the After Trigger and Instead of Trigger with example. I hope after reading this article your sql triggers concepts will be strong. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

About the Author
Shailendra Chauhan works as Sr. Software Engineer at reputed MNC and has more than 5 years of hand over Microsoft .NET technologies. He is a .NET Consultant and is the founder & chief editor of www.dotnet-tricks.com and www.dotnetinterviewtricks.com blogs. He is an author of books ASP.NET MVC Interview Questions and Answers & LINQ Interview Questions and Answers.
He loves to work with web applications and mobile apps using Microsoft technology including ASP.NET, MVC, C#, SQL Server, WCF, Web API, Entity Framework,Cloud Computing, Windows Azure, jQuery, jQuery Mobile, Knockout.js, Angular.js and many more web technologies. More...
 
 
Recommended Articles!
 
 
Search Articles
 
 
Subscribe & follow Us
 
 
Protected by Copyscape Web Plagiarism Check
 
Free ASP.NET MVC Interview Book
Learn In Hindi
Browse By Category
 
Recent Articles
 
Popular Articles
 
Like us on Facebook
 
Featured AD
Join our Training programs in Delhi/Noida on ASP.NET MVC, WCF, EF, jQuery - call us    +91-9871749695