Sql-server – Update trigger fired before insert trigger upon insert of new row

sql-server-2012sql-server-2016trigger

I have a table FOO and 2 triggers defined as follows

CREATE TRIGGER [dbo].[FOO_UPDATED]
   ON [dbo].[FOO]
   AFTER UPDATE
AS
BEGIN
   ...
END

and

CREATE TRIGGER [dbo].[FOO_INSERTED]
   ON [dbo].[FOO]
   AFTER INSERT
AS
BEGIN
   ...
END

The triggers were working fine on SQL Server 2012 – FOO_INSERTED was firing upon insert and FOO_UPDATED was firing upon update.

But now I migrated the database on SQL Server 2016 (no change in the structure and data, simple backup and restore). Upon insert of 1 new row in FOO, both triggers fire in order: first FOO_UPDATED, then FOO_INSERTED.

I've looked around for similar problems and found only that one topic: SQL Server: update trigger fires before insert trigger

Does anyone have an idea what could be the reason for this strange behavior?

Best Answer

Solved: I have update statements for the same table inside the body of the INSERT trigger. Nested triggers are disabled on my database on SQL Server 2012 but after the restore on SQL Server 2016, the setting was somehow enabled.

Edit: It appears that the 'nested triggers' setting of the SQL Server instance was set to 'False' on SQL12 and to 'True' on SQL16.

Related Question