CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
SQLGUy
SQLGUy

Reputation: 13

Trigger not calling other trigger

Using SQL-server.

I have 2 tables and 2 update triggers.

1st trigger gets called on an update to table A. 2nd trigger gets called on an update to table B.

Both triggers fire fine when updating the tables individually

Here's the issue: the trigger for table A forces on update to table B. At this time, the 2nd trigger never fires. I'm not sure why not? But if I make a change to table B manually (not through the trigger), the 2nd trigger does fire.

My set up: I'm using cursors as I want row level triggers. I'm also using the keyword 'FOR' for before update transactions.

Upvotes: 1

Views: 176

Answers (1)

JotaBe
JotaBe

Reputation: 39014

The answer is that you need to configure the behavior of nested triggers:

Configure the nested triggers Server Configuration Option

When nested triggers is set to 0, AFTER triggers cannot cascade. When nested triggers is set to 1 (the default), AFTER triggers can cascade to as many as 32 levels. INSTEAD OF triggers can be nested regardless of the setting of this option.

Upvotes: 1

Related Questions