brettstahlman (3/13/2013)
If I understand correctly, the recursion is indirect, and hence, should not be prevented by RECURSIVE_TRIGGERS disabled (the setting in my database).
I do not think it is considered indirect. I have no documentation to point to, only evidence. If you enable recursive triggers and run your test you'll quickly see an error like:
Msg 217, Level 16, State 1, Procedure YourAfterTriggerName, Line 5
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
USE [master]
GO
-- try it with it ON and you'll see a recursive limit error
--ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
GO
USE [YourDatabaseName]
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.maintable')
AND type IN ( N'U' ) )
DROP TABLE dbo.maintable;
GO
CREATE TABLE dbo.maintable ( triggername SYSNAME );
GO
CREATE TRIGGER dbo.instead_insert_maintable ON dbo.maintable
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO maintable
VALUES ( OBJECT_NAME(@@PROCID) );
END
GO
CREATE TRIGGER dbo.after_insert_maintable ON dbo.maintable
AFTER INSERT
AS
BEGIN
INSERT INTO maintable
VALUES ( OBJECT_NAME(@@PROCID) );
END
GO
INSERT INTO dbo.maintable
( triggername )
VALUES ( N'test' )
GO
SELECT *
FROM dbo.maintable
go
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato