Triggers to Execute in Pre Defined Order
-
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/ -
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
-
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! -
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
--------------------------------
-
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
Learn to play, play to learn !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[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply