Recursive behavior of triggers in SQL Server 6.5 is incorrect (304364)

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 "nested triggers" 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.

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