Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Triggers to Execute in Pre Defined Order Expand / Collapse
Author
Message
Posted Monday, March 20, 2006 1:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:48 AM
Points: 912, Visits: 210
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/
Post #267062
Posted Thursday, April 6, 2006 3:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2007 2:32 AM
Points: 1, 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

Post #271471
Posted Thursday, April 6, 2006 6:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 9, 2006 12:43 PM
Points: 20, 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!
Post #271515
Posted Thursday, April 6, 2006 11:53 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:21 AM
Points: 229, Visits: 170

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



--------------------------------
Post #271806
Posted Friday, April 7, 2006 12:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:21 AM
Points: 6,743, Visits: 8,517

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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #271812
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse