Hi,

I have 3 Tables with After Update Triggers.
They all cascade like City -> Province -> State (as a simplified example)

If I Update a bit (A) in City, a Trigger Sets a bit (B) in Province.
If I Update the bit B in Province, a Trigger Sets a Bit (C) in State.

This works.

However:

If I Set A, a trigger on table City sets B BUT a trigger on table Province
DOESN'T in turn Set C in table State.

So: Individually the Triggers work, but they don't Cascade their action.

Any advise?

TIA,


Michael

Hi Michael,

I think I'm having the same problem.  I'm hoping to take action via a
trigger on child records when the parent is deleted, but the child table's
trigger doesn't seem to fire.  Hopefully someone has a suggestion.

Rgds,
Bill

Show quoteHide quote

There is a server option "Allow triggers to be fired which fire other
triggers (nested triggers)". Here is the description copied from BO:
1. Expand a server group.
2. Right-click a server, and then click Properties.
3. Click the Server Settings tab.
4. Under Server behavior, select or clear the Allow triggers to be
fired which fire other triggers (nested triggers) check box.

For more information, see Books Online, article "Using Nested Triggers"

Hi Sergei,

Thanks for your advise!

I just found an article on this:

http://www.sqlservercentral.com/columnists/bkelley/triggers_1.asp

Nested and Recursive Triggers
Nested triggers are triggers that fire due to actions of other triggers. 
For instance, I delete a row from TableA.  A trigger on TableA fires to
delete rows from TableB.  Because I'm deleting rows from TableB, a trigger
fires on TableB to record the deletes.  This is an example of a nested
trigger.  As we've talked about, SQL Server 7.0 doesn't support cascading
updates and deletes based on foreign key relationships.  Therefore, if we
want to relate our data we can't use DRI and must resort to triggers or some
application oversight.  Let's say we've got a cascade delete to fire down 3
or 4 tables.  Nested triggers are our answer.  Our delete on a particular
table fires a trigger which deletes rows for another table, which fires a
trigger, so on and so forth.  SQL Server 7 and 2000 support up to 32 levels
of nested triggers.

Now the big question is, does my SQL Server allow nested triggers?  That's
an easy question to answer.  It's on by default, but in Query Analyzer we can
issue the following command:

EXEC sp_configure 'nested triggers'

If your run_value is set to 0, your server isn't allowing nested triggers. 
If it's set to 1, nested triggers may fire.  This is a server wide setting. 
Now, to change your setting, once again use the sp_configure command:

To turn off nested triggers:

EXEC sp_configure 'nested triggers', 0
RECONFIGURE

To turn on nested triggers:

EXEC sp_configure 'nested triggers', 1
RECONFIGURE




Show quoteHide quote

I wonder why the defualt setting is off for this.  (Protecting us from
potentially faulty programming perhaps?) ;-)  Also, seems like it would be
handy to be able to turn it on per table or database, rather than at the
server level.

In my case where I'm just trying to perform some actions first with the
information in detail records that will be deleted, would it be better to
turn off the cascade delete for the related table and handle deleting of the
detail records in the delete trigger of the master table, or is it better to
allow nested triggers?

Show quoteHide quote

Hi Bill,

What scares me is that someone or something else can disable your nested
triggers because it's DataServer-Wide :-(((

Show quoteHide quote

I wonder why the defualt setting is off for this.  (Protecting us from
potentially faulty programming perhaps?) ;-)  Also, seems like it would be
handy to be able to turn it on per table or database, rather than at the
server level.

In my case where I'm just trying to perform some actions first with the
information in detail records that will be deleted, would it be better to
turn off the cascade delete for the related table and handle deleting of the
detail records in the delete trigger of the master table, or is it better to
allow nested triggers?

Show quoteHide quote

Torn over a normalizing decision
Mulitple Create Views in Query Batch
Strange Timeout problem.
Book Recommendations
Question about functions