Nested Triggers In SQL Server

Question

Using SQL Server 2012, I have devised a system which uses 2 tables and 2 triggers. Each table has 1 trigger.

Triggers fire AFTER INSERT.

Tables A and B , Triggers a and b

When a record is inserted into Table A , trigger a inserts a record into Table B.

When a record is inserted into Table B , trigger b sends me an email.

The trouble is that trigger b only works when I manually insert records into Table B. When trigger a does the insert, Trigger b does not fire.

I have simplified trigger b to a update a third test-table. trigger b still does not fire, when trigger a does the insert, so it seems unlikely that the email function is to blame.

when does Trigger A fire? AFTER INSERT or INSTEAD of INSERT? AFTER Triggers will only cascade if the Server configuration 'nested triggers' is set to 1 — CPMunich
trigger fires AFTER INSERT on TABLE a. I'll update question. — GRY

alan07sl alan07sl · Accepted Answer · 2016-01-12T15:36:49

You need to set nested triggers to 1

USE AdventureWorks2012 ;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'nested triggers', 1 ;
GO
RECONFIGURE;
GO

Nested Triggers In SQL Server

1 Answers