Update trigger fired before insert trigger upon insert of new row

Question

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?

Answer 1

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.

Answer 2

It appears that what is happening is your insert occurs in this order:

1. data is inserted into table, which activates the AFTER INSERT trigger.
2. the AFTER INSERT trigger is executing DML (data manipulation language) actions that trigger the AFTER UPDATE trigger
3. the AFTER UPDATE trigger completes
4. the AFTER INSERT trigger completes
5. the transaction (insert and triggers combined) completes

The best work-around I can find via my Google-fu is to merge the triggers into something like:

CREATE TRIGGER foo_AlteredRecord ON [dbo].[Foo]
  AFTER INSERT, UPDATE, DELETE
AS
BEGIN

   Declare @DelCount int;
   Declare @InsCount int;

   SELECT @InsCount = Count(Col1) FROM INSERTED;
   SELECT @DelCount = Count(Col1) FROM DELETED;

   If @InsCount > 0 and @DelCount = 0
   Begin
      -- At least 1 row inserted. Your Insert Trigger logic here
   End
   Else If @DelCount > 0 and @InsCount = 0
   Begin
      -- at least 1 row deleted. Your Delete Trigger logic here
   End 
   Else If @DelCount > 0 and @InsCount > 0
   Begin
      -- old row deleted, new row inserted; both indicates an update.
      -- your update logic here.  
   End 
End 

If you don't need any delete logic, you can remove the Delete from the AFTER clause, but you still need to get the Delete row count to test for the final condition in the IF block.