|
|
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
Navigation:
[Reply to this message]
|