Microsoft KB Archive/124015

= INFO: Trigger Execute Even if No Rows Affected by SQL Statement =

Article ID: 124015

Article Last Modified on 2/22/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q124015



SUMMARY
An insert, update, or delete trigger will be executed even if the SQL statement referencing the trigger table did not affect any rows.



MORE INFORMATION
If the conditions of an INSERT, UPDATE, or DELETE statement result in an empty result set, a trigger associated with the table in the statement will still be executed.

If this behavior needs to be accounted for in the trigger source code, the trigger developer can examine the @@ROWCOUNT global variable as the first statement in the trigger. Remember that any Transact-SQL statement that does not return rows (such as an IF statement) sets @@ROWCOUNT to 0. An alternative to this approach is to examine the count of rows in the inserted and/or deleted table depending whether the trigger is for insert, update, or delete purposes.

For additional information on the behavior of the IF UPDATE statement under these conditions, please see the following article in the Microsoft Knowledge Base:

64238 : PRB: IF UPDATE Trigger with No Rows Changed

Additional query words: inserted deleted

Keywords: kbinfo kbprogramming kbusage KB124015

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.