Well-behaved triggers

I frequently here programmers lamenting the use of SQL triggers.  To some they seem to be an evil to be avoided at all costs, and while I certainly agree that their use should be minimized, there are still instances where a trigger is the best approach.

In my experience, much of the fear of triggers seems to stem from poorly written triggers.  There is definitely a "right" way to do it and a bunch of wrong ways to do it.  Unfortunately, the standard trigger templates that ship with SSMS tend to lean to the wrong way.

So let's start with the basic trigger script from SSMS.

CREATE TRIGGER dbo.MyTrigger
ON dbo.MyTable
AFTER UPDATE
AS
UPDATE dbo.MyTable
SET ModifiedDate = GETDATE()
FROM inserted i
WHERE i.CustomerID = MyTable.CustomerID
GO

This looks simple and innocuous enough.  Ponder the following considerations, however:
1. Who wrote this and why?
2. What happens when 1000 records are bulk updated in MyTable?
3. What if I only need to update MyTable when CustomerID is one of the fields that was updated?
4. How do I stop my update inside the trigger from triggering the update trigger again?
5. Since this trigger could slow down performance, how do I prevent this trigger from running from certain other procs?

Here is a much better script:

CREATE TRIGGER dbo.cstr_MyTable_UPDATE
ON dbo.MyTable
AFTER UPDATE
AS
/*
Company

Description:

History:
20160608 djedziniak initial version
*/
BEGIN
SET NOCOUNT ON;
IF(UPDATE({COLUMN}))
BEGIN
DECLARE C_INSERTED CURSOR FOR
SELECT {columns}
FROM inserted
WHERE {WHERE CLAUSE}
OPEN C_INSERTED
FETCH NEXT FROM C_INSERTED INTO {VARIABLES}
WHILE @@FETCH_STATUS=0
BEGIN

FETCH NEXT FROM C_INSERTED INTO {VARIABLES}
END
CLOSE C_INSERTED
DEALLOCATE C_INSERTED
END
END
GO

The line IF(UPDATE({COLUMN})) checks to see if a column has been updated in the inserted table.  If it has, this function returns true.  This is how we can prevent our logic from running unless certain data was updated.

Notice that there is a cursor defined here.  That is because the inserted table could have many records in it.  A common mistake is assuming that it only contains a single record.  When possible, you should avoid using a cursor.  In our case, we can eliminate the cursor and just do a bulk update.

CREATE TRIGGER dbo.cstr_MyTable_UPDATE
ON dbo.MyTable
AFTER UPDATE
AS
/*
Company

Description:

History:
20160608 djedziniak initial version
*/
BEGIN
SET NOCOUNT ON;
IF(UPDATE(CustomerID))
BEGIN
UPDATE dbo.MyTable
SET ModifiedDate = GETDATE()
FROM inserted i
WHERE i.CustomerID = MyTable.CustomerID
END
END
GO

Now if I want to stop this trigger from running itself (and potentially creating a Cartesian product), I need to add context info.

CREATE TRIGGER dbo.cstr_MyTable_UPDATE
ON dbo.MyTable
AFTER UPDATE
AS
/*
Company

Description:

History:
20160608 djedziniak initial version
*/
BEGIN
SET NOCOUNT ON;

if Context_Info()=0x55555 return
set Context_Info 0x55555

IF(UPDATE(CustomerID))
BEGIN
UPDATE dbo.MyTable
SET ModifiedDate = GETDATE()
FROM inserted i
WHERE i.CustomerID = MyTable.CustomerID
END

set Context_Info 0x00000

END
GO

What the first red line is doing is checking a global variable for a binary value.  If that value is set, the trigger returns without doing anything.  This line can also be used to prevent the trigger from running when an update is made from some other object.  That object would first set the context info, so the update, then clear it.
The two set lines are setting the variable before the update and clearing it after the update.

So now this trigger is much safer than the default basic trigger template.
I am handling bulk operations, restricting my operations based on certain fields being updated, and using context info to prevent the trigger from calling itself.

If you have any other tips for making triggers more well-behaved, post them here!