• 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