Microsoft KB Archive/304364

= Recursive behavior of triggers in SQL Server 6.5 is incorrect =

Article ID: 304364

Article Last Modified on 12/27/2004

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q304364



SYMPTOMS
With SQL Server 6.5, a trigger cannot recursively call itself. A trigger does not call itself in response to a second update to the same table within the trigger. For example, if an UPDATE trigger on one column of a table results in an update to another column, the UPDATE trigger activates only once rather than repeatedly.

However, in SQL Server 6.5, you can circuitously implement a recursive trigger if the trigger executes a stored procedure that updates the same table. The implementation causes the trigger to fire repeatedly if &quot;nested triggers&quot; is enabled.

The circuitous implementation of a recursive trigger with a stored procedure is not supported in SQL Server 6.5 and should not be used.

In SQL Server 7.0 or later, you can use recursive triggers if the database option RECURSIVE TRIGGER is set to ON. When the RECURSIVE TRIGGER option is set ON, updates that use either Data Manipulation Language (DML) statements (UPDATE, DELETE, INSERT) or stored procedures within the trigger against the same table fire the trigger again. Setting the RECURSIVE TRIGGER option to OFF disables all recursion, including any that is initiated by the trigger stored procedure.



WORKAROUND
To work around the problem:
 * If you are migrating from SQL Server 6.5, the application may be able to use true indirect trigger recursion in SQL Server 7.0 or later.


 * You may be able to use an INSTEAD OF trigger for SQL Server 2000 or later.



MORE INFORMATION
Here is an example of an UPDATE trigger that calls a stored procedure that updates the same table: create table myTbl (a int)  -- table go create proc myProc          -- stored procedure as update myTbl set a = 1 go create trigger myTrig on myTbl -- trigger for update as print 'trigger fired ...' exec myProc go insert myTbl values(2) go sp_configure 'nested triggers',1 -- this needs to be enabled reconfigure with override go update myTbl set a = 2 go

The circuitous implementation of trigger recursion in SQL Server 6.5 should not be confused with &quot;indirect recursion&quot; in SQL Server 7.0 or later. This circuitous implementation of a recursive trigger is called &quot;direct recursion&quot;, whereas &quot;indirect recursion&quot; in SQL Server 7.0 involves a trigger that updates another table whose trigger could update the original table, thus causing the original trigger to fire again. Refer to SQL Server Books Online for more details and examples.

In SQL Server 7.0 and later, you can use the RECURSIVE TRIGGERS database option to enable or disable &quot;direct recursion.&quot; You use the SQL Server NESTED TRIGGERS configuration to enable or disable &quot;indirect recursion.&quot;

Keywords: kbprb KB304364

-

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

© Microsoft Corporation. All rights reserved.