Defensive database programming: fun with triggers.

There are three kinds of triggers: those which blow up and those which fail silently ;). Seriously, there are three all-too-common kinds of problems related to triggers:

• ·         Triggers can fail to work as expected
• ·         Triggers can be bypassed
• ·         Triggers can expose problems in other code, such as T-SQL code written under the assumption that there are no triggers

Fortunately there already are lots of good articles and posts written on this subject, so I don’t have to describe it all by myself. Still, there is a lot to be said, and there is no way I could squeeze it all into a single post. As usual in this series, I will concentrate only on correctness of the results, I will not consider the performance. Also I do not intend to list all possible kinds of problems, but only the ones about which I know and consider them worth mentioning. As usual, comments and additions are welcome.

Disclaimer: don’t get me wrong – I do not object to using triggers, I use them myself in my system. However, if you do not use triggers properly, you can expose your system to several potential problems. So you need to be aware of these potential problems when you develop robust triggers.

Triggers that fail to work as expected

Triggers should not assume that statements can modify only one row at a time

This may sound trivial, but this seems to be the most common mistake. You can skip this chapter if you are already familiar with the problem.

Some triggers are coded under the assumption that only one row can be inserted/updated/deleted at a time. Such assumptions are usually unfounded. In fact, I have never seen a genuine business requirement prohibiting multi-row changes. However, apparently the most common problem with beginners’ triggers is their (triggers) inability to handle multi-row modifications. For example, consider the following table and a trigger which is supposed to populate it:

CREATE TABLE ChangeLogs.SomeDataLog(ID INT, Change VARCHAR(30),

  ChangedBy VARCHAR(30), ChangeTime DATETIME);

GO

CREATE TRIGGER SomeData_Ins ON Data.SomeData

FOR INSERT

AS

DECLARE @ID INT;

SET @ID = (SELECT ID FROM INSERTED);

INSERT INTO ChangeLogs.SomeDataLog(ID, Change,

  ChangedBy, ChangeTime)

VALUES(@ID, 'Inserted', SUSER_NAME(), Getdate());

Clearly this trigger cannot handle multi-row inserts:

DELETE FROM data.SomeData;

INSERT INTO data.SomeData(ID, AnotherID)

SELECT 1,2 UNION ALL SELECT 3, 4;

Msg 512, Level 16, State 1, Procedure SomeData_Ins, Line 5

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

Although the trigger blew up, at least you have received an error message indicating that you have a problem. The following trigger will silently fail, logging just one insert:

CREATE TRIGGER SomeData_Ins ON Data.SomeData

FOR INSERT

AS

DECLARE @ID INT;

SELECT @ID = ID FROM INSERTED;

INSERT INTO ChangeLogs.SomeDataLog(ID, Change,

  ChangedBy, ChangeTime)

VALUES(@ID, 'Inserted', SUSER_NAME(), Getdate());

Apparently this is a very common, although trivial, mistake. The correct solution is very simple:

CREATE TRIGGER SomeData_Ins ON Data.SomeData

FOR INSERT

AS

INSERT INTO ChangeLogs.SomeDataLog(ID, Change,

  ChangedBy, ChangeTime)

SELECT ID, 'Inserted', SUSER_NAME(), Getdate() FROM INSERTED;

Understanding RECURSIVE_TRIGGERS setting and using it properly

If RECURSIVE_TRIGGERS setting is ON, then if your trigger modifies the same table, those modifications will also fire triggers. This can be used to modify data recursively. For example, consider the following table and sample data:

CREATE TABLE Data.Nodes(NodeID INT NOT NULL,

  ParentID INT NULL,

  Status VARCHAR(20));

GO

INSERT INTO Data.Nodes(NodeID,

  ParentID,

  Status)

SELECT 1, NULL, 'Sleeping' UNION ALL

SELECT 2, 1, 'Sleeping'  UNION ALL

SELECT 3, 2, 'Sleeping';

GO

Suppose that if a node becomes active, all its ancestors must also become active. The following trigger attempts to implement this business rule:

CREATE TRIGGER Nodes_Upd ON Data.Nodes

FOR UPDATE

AS

IF EXISTS(SELECT 1 FROM INSERTED) BEGIN

  UPDATE Data.Nodes SET Status = 'Active'

    WHERE NodeID IN(SELECT ParentID FROM INSERTED WHERE Status = 'Active');

END

For hierarchies without cycles, and less than 32 levels deep, this approach works, provided that RECURSIVE_TRIGGERS setting is ON:

ALTER DATABASE Test SET RECURSIVE_TRIGGERS ON;

UPDATE Data.Nodes SET Status = 'Sleeping';

UPDATE Data.Nodes SET Status = 'Active'

WHERE NodeID = 3;

SELECT NodeID, Status FROM Data.Nodes

NodeID      Status

----------- --------------------

1           Active

2           Active

3           Active

 However, if you use this approach, you need to ensure that:

·         RECURSIVE_TRIGGERS setting is ON

·         Your data does not have cycles

·         Your hierarchies are less than 32 levels deep

 The first condition is obvious. The second one needs an example. Consider the following hierarchy with a cycle:

INSERT INTO Data.Nodes(NodeID,

  ParentID,

  Status)

SELECT 4, 5, 'Sleeping'  UNION ALL

SELECT 5, 4, 'Sleeping';

UPDATE Data.Nodes SET Status = 'Active'

WHERE NodeID = 5;

As one node becomes active, the trigger begins an infinite loop and hits the 32-level limit:

INSERT INTO Data.Nodes(NodeID,

  ParentID,

  Status)

SELECT 4, 5, 'Sleeping'  UNION ALL

SELECT 5, 4, 'Sleeping';

UPDATE Data.Nodes SET Status = 'Active'

WHERE NodeID = 5;

Msg 217, Level 16, State 1, Procedure Nodes_Upd, Line 5

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

You can easily fix the trigger so that it tolerates cycles, as follows:

DROP TRIGGER Data.Nodes_Upd;

GO

CREATE TRIGGER Nodes_Upd ON Data.Nodes

FOR UPDATE

AS

IF EXISTS(SELECT 1 FROM INSERTED) BEGIN

  UPDATE Data.Nodes SET Status = 'Active'

    WHERE NodeID IN(SELECT ParentID FROM INSERTED WHERE Status = 'Active')

    AND Status <> 'Active';

END

However, the best practice is to ensure the integrity of your data rather than to develop workarounds because your data has problems. This is why I am suggesting that the data should have no cycles. Describing how to accomplish that is beyond the scope of this post.

Finally, let me provide a short example demonstrating how this trigger blows up if the hierarchy is more than 32 levels deep:

TRUNCATE TABLE Data.Nodes;

SET NOCOUNT ON;

DECLARE @ID INT;

SET @ID = 100;

WHILE @ID<200 BEGIN

  INSERT INTO Data.Nodes(NodeID,

    ParentID,

    Status)

  SELECT @ID, CASE WHEN @ID=100 THEN NULL ELSE @ID-1 END, 'Sleeping';

  SET @ID = @ID + 1;

END

UPDATE Data.Nodes SET Status = 'Active'

WHERE NodeID = 199;

Msg 217, Level 16, State 1, Procedure Nodes_Upd, Line 4

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

As you have seen, you need to be very careful when you work with recursive triggers.

In 2005 and later versions, triggers should take in account snapshot isolation

SQL Server MVP Hugo Kornelis has demonstrated the problem:

http://sqlblog.com/blogs/hugo_kornelis/archive/2006/07/26/Snapshot-and-integrity-part-2.aspx

 and posted a workaround:

http://sqlblog.com/blogs/hugo_kornelis/archive/2006/08/25/snapshot-isolation-a-threat-for-integrity-part-3.aspx

In 2008, triggers should not rely on @@ROWCOUNT the way they could in 2005 and before

 Recently SQL Server MVP Steve Kass described the problem here:

http://sqlblog.com/blogs/steve_kass/archive/2009/04/29/It_2700_s-2008-and-rowcount-ain_2700_t-what-it-used-to-be.-Don_2700_t-use-it-in-triggers_2100_.aspx

Triggers can render transactions doomed, making it impossible to rollback to a savepoint

I have demonstrated the problem in this post:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/11/15/avoid-mixing-old-and-new-styles-of-error-handling.aspx

Another trigger can undo your trigger’s work

If you have multiple triggers which are created on the same table and which fire on the same operation, one trigger may overwrite another trigger’s modifications. For instance, the following trigger will deliberately undo the changes done by the previous trigger (SomeData_Ins):

CREATE TRIGGER Undo_Changes_By_SomeData_Ins ON Data.SomeData

FOR INSERT

AS

DELETE FROM ChangeLogs.SomeDataLog WHERE ID IN(

  SELECT ID FROM INSERTED);

Usually this problem does not manifest itself so clearly as in this obvious example.

In my next post I will describe some situations when triggers are bypassed and when they expose problems in other T-SQL code.

This post continues my series on defensive database programming. My next post:

Here are my previous posts  from the series: 

Defensive database programming: fun with ROWCOUNT

Summarizing previous posts about defensive database programming

Defensive database programming: SET vs. SELECT.

Stress testing UPSERTs

Defensive database programming: fun with UPDATE.

Defensive database programming: eliminating IF statements.

Defensive database programming: fun with changing column widths.

Avoid mixing old and new styles of error handling.

Defensive database programming: adding ESCAPE clauses.

Defensive database programming: qualifying column names.

Defensive database programming: rewriting queries with NOT IN().