SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Triggers to Execute in Pre Defined Order


Triggers to Execute in Pre Defined Order

Author
Message
Dinesh Asanka
Dinesh Asanka
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3902 Visits: 223
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dasanka/triggerstoexecuteinpredefinedorder.asp






My Blog: http://dineshasanka.spaces.live.com/
Jeremy Swift
Jeremy Swift
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 1

Big thing to watch out for if trying to use the ordering property is that replication will hijack the FIRST setting for its own trigger.

SBO: "Replication automatically generates a first trigger for any table that is included in an immediate updating or queued updating subscription. Replication requires that its trigger be the first trigger. Replication raises an error when you try to include a table with a first trigger in an immediate updating or queued updating subscription. If you try to make a trigger a first trigger after a table has been included in a subscription, sp_settriggerorder returns an error. If you use ALTER on the replication trigger or use sp_settriggerorder to change the replication trigger to a last or none trigger, the subscription will not function correctly."

Also modifying a trigger unsets the ordering.

SBO: "If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute is dropped and the order value is set to None. The order must be reset by using sp_settriggerorder"

I'm struggling with the primitive trigger capabilities of SQL Server 2005, having come from a Sybase ASA background where you have the luxury of multiple before and after triggers, each of which can be given a unique execution priority and each of which can be defined as operating at row level or trigger (ie set) level. They also have an update trigger variant which is fired only on change in one or more listed columns. All these features mean that triggers can be kept small and simple and having them they get used. I hope Microsoft are working towards similar elegance in a future release.

Hope these thoughts enhance the collective DNA


elarrat
elarrat
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 1

I didn't see the table TriggerName:

(I created it like this

CREATE TABLE [TriggerName] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TriggerName] [varchar](21) NULL
) ON [PRIMARY]
GO

Elarrat, JIA * o Portimoteco!
(From Amazon Jungle - Porto de Moz-Xingu-PA-Brazil)




Elarrat, JIA,
o Portimoteco!
Jeswanth Jaishanker
Jeswanth Jaishanker
Right there with Babe
Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)

Group: General Forum Members
Points: 748 Visits: 172

Is there any way to set order for triggers if teh same is more than 3-4 . coz i have a table which fires more than 12 triggers.

Jeswanth



--------------------------------
ALZDBA
ALZDBA
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47901 Visits: 9075

now that's where the cautions come in ...

12 triggers on one object, may be a bit _to_ many
Maybe there is a way to redesign them to less.

The general guideline is triggers should not be order-dependant !

Like Dinesh Asanka menetioned in this article, these settings are not scripted and are very vulnarable (e.g. alter trigger "resets" them) !

Actualy I'm happy Dinesh shared this little way of influence we may use in strict cases.



Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search