INF: Trigger Execute Even if No Rows Affected by SQL Statement

 Last reviewed: April 30, 1997
Article ID: Q124015

The information in this article applies to:
  • Microsoft SQL Server version 4.2 for OS/2
  • Microsoft SQL Server, version 4.2

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

The definition for the CREATE TRIGGER command on page 245 of the "Transact- SQL Reference" states: 

   A trigger is executed automatically when a user attempts the
   specified data-modification statement on the specified table.
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: 

   ARTICLE-ID: Q64238
   TITLE     : PRB: IF UPDATE Trigger with No Rows Changed

Additional query words: inserted deleted
Keywords : kbprg kbusage SSrvServer SSrvWinNT
Version : 4.2 | 4.2 4.2a 4.2b 4.21 4.21a
Platform : OS/2 WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.