That seems to be the ticket. The instead trigger is fired once, and the after trigger is what continues to recurse.
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS ON 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
IF @@nestlevel < 20
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