A Design Pattern For Nested Triggers?

Ever since sql server 2000 appeared, we've had the ability to create multiple triggers on a single table. Until a problem I saw at a site the other day though, I'd never REALLY stopped and thought about the consequences of using multiple update triggers. In this case, one dev had added a trigger to maintain a denormalised table like so:

CREATE TRIGGER TR_MyTable_FullName_Update ON MyTable FOR UPDATE AS

UPDATE MyTable SET FullName = FirstName + ' ' + LastName FROM MyTable INNER JOIN Inserted

ON MyTable.MyTableKey = Inserted.MyTableKey

Apart from the issues with assuming name order, etc. and the fact that this would now have been better done with a calculated column, the problem is that another dev then added a trigger on a LastModified column:

CREATE TRIGGER TR_MyTable_LastModified_Update ON MyTable FOR UPDATE AS

UPDATE MyTable SET LastModified = GETDATE() FROM MyTable INNER JOIN Inserted

ON MyTable.MyTableKey = Inserted.MyTableKey

Simple enough but the table can no longer be updated because an infinite loop then exists between the two triggers. In this case, it will be broken by the maximum nesting level of 32 and the transaction rolled back. Now there are a couple of solutions to this:

1. Disable nested triggers - not a good option for this system for other reasons

2. Test the nesting level in at least one of the triggers and don't fire if a maximum level is exceeded. The problem with this is that the trigger that has the limit needs to know at design time, how many other triggers there are on the table.

Tibor Karaszi suggested a mod to the first trigger like:

CREATE TRIGGER TR_MyTable_FullName_Update ON MyTable FOR UPDATE AS

UPDATE MyTable SET FullName = FirstName + ' ' + LastName FROM MyTable INNER JOIN Inserted

ON MyTable.MyTableKey = Inserted.MyTableKey

WHERE MyTable.FullName <> (MyTable.FirstName + ' ' + MyTable.LastName)

This is a reasonable solution in this case. If the LastModified trigger fired first, it still means it would fire twice which isn't desirable. SQL Server does allow you control over which trigger fires first and which fires last but not the order of any in between. This could help in this case.

This got me thinking that there really is a need for a design pattern for multiple update triggers. I've not seen this discussed in any of the training materials or in BOL in any depth. Erland Sommarskog suggested to me that he thinks it would be desirable to be able to indicate:

1. The order in which the triggers fire.

2. If a particular trigger should cause other triggers to fire.

This is a good idea but I think it needs to go further. I think we need a way to group triggers that are unrelated and to be able to indicate that a particular group of triggers only fire once. I'd be interested if anyone has seen any other discussion on this.