.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Ordering trigger

Posted By:nishithraj       Posted Date: November 28, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  

If there are multiple triggers to be fired in a same table, SQL Server has a provision to order them. Mostly this is used to prioritize them.

To achieve this we have to use the system trigger sp_settriggerorder. This should not be used for INSTEAD OF triggers (Only with AFTER).

exec sp_settriggerorder @triggername = , @order = [FIRST|LAST|NONE],@stmttype = [INSERT|UPDATE|DELETE|], @namespace = [DATABASE|SERVER|NULL] 

Here stmttype is the type of the trigger.


Create a table to have two triggers first.

create table dbo.tblemployee (empid int identity primary key, empname varchar(100))
create trigger dbo.tr_employee_1 on dbo. tblemployee
for insert
set nocount on
print 'trigger1 started'


create trigger dbo.tr_ employee _2 on dbo. tblemployee
for insert
set nocount on
print 'trigger2 started'

Execute above two triggers in the table.

insert into dbo.tblemployee(empid, empname) values(1,'John')

By default the message has the following values

trigger1 started'

trigger2 started'

to change the order of the triggers, the trigger sp_settriggerorder needs to be called


exec sp_settriggerorder @triggername = 'tr_employee_1', 
@order = 'first',
@stmttype = 'insert',
@namespace = null

exec sp_settriggerorder @triggername = tr_employee_1',
@order = 'last',
@stmttype = 'insert',
@namespace = null



 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend