Sunday 30 June 2013

Is it possible to set trigger priority in sql server?

Yes.
    We know trigger is event which is implicitly called on any kind of DML or DDL operation occurred. It may be interview question Suppose I have one table and I created three trigger on that table for update. So which trigger will be fired when?
    If you dont know this concept you will say it will be fiired  randomly.But we can fire it as per our requirement.
  In sql server we have  sp_settriggerorder which will help us to set triggger priority.

sp_settriggerorder 'triggername','value', 'statement_type' 
 
Argument :
trigger Name : Name of trigger
 
value  : It has three value 
First Trigger is fired first.
Last Trigger is fired last.
None Trigger is fired in undefined order.

 
 statement_type:
 
 Specifies the SQL statement that fires the trigger.
like  INSERT, UPDATE, DELETE.

USE  Your_Database; 
GO 
sp_settriggerorder @triggername= 'TriggerName', @order='Type', @stmttype = 'UPDATE';




No comments:

Post a Comment

First Database In Sql Server