Posted by Erland Sommarskog on 10/01/77 11:39

(teddysnips@hotmail.com) writes:
> What we are finding is that if an update is made on the tblTSRule
> table, it fires TWO triggers - first the UPDATE trigger on the
> tblTSRule table (which updates the tblTSEmpRules table) and then the
> UPDATE trigger on the tblTSEmpRules table. The two triggers are
> virtually identical, but what can we do in terms of design to get
> around this? I should point out that the ability exists in the
> application to amend both rules and the assignment of rules to
> employees.

There are a couple of options.

1) Set the configuration option "nested triggers" to 0. But this is a
server-wide option. I recommend that you leave it on.

2) In the trigger tblTSEmpRules add:

IF NOT EXISTS (SELECT * FROM inserted WHERE
fldLowerBound IS NULL OR fldUpperBound IS NULL)
RETURN

3) In the trigger TSRules add:

CREATE TABLE #no$cascade(a int NOT NULL)

And in the other trigger add:

IF object_id('tempdb..#no$cascade') IS NOT NULL
RETURN


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]