Conflicting Triggers - Looking for a workaround

jdana
jdana used Ask the Experts™
on
I inherited an Access / SQL Server 2005 DB a few years back.  I cleaned it up a lot since then, but, frankly, the database schema is still funky in some areas.  The database contains two tables, tblCase and tblCaseStatus that should be a single table, but they aren't.  The tables are joined on the column, CaseID.  I've replicated a column in both tables named Cancel.  (This is sloppy design, but the replicated column allows me to NOT re-write about 100 lines of code.)  

I built a couple triggers to make tblCase.Cancel match tblCaseStatus.Cancel.  The triggers are shown below.  Separately, each one works just fine.  Together, they conflict.  Here's why: If TR_tblCaseReplicateCancel runs, then TR_tblCASEStatusReplicateCancel runs, which makes TR_tblCaseReplicateCancel want to run again...

Can anyone think of a clever workaround to prevent this iterative effect?
USE MyDatabase
GO
CREATE TRIGGER TR_tblCaseReplicateCancel
ON [dbo].[tblCase]
FOR INSERT, UPDATE
AS
BEGIN
	DECLARE 
		@CaseID int,
		@Cancel bit
	SELECT @CaseID = CaseID, @Cancel = Cancel FROM inserted
	UPDATE [dbo].[tblCASESTATUS] SET Cancel = @Cancel WHERE CaseID = @CaseID
END
GO
USE Casetrack
GO
CREATE TRIGGER TR_tblCASEStatusReplicateCancel
ON [dbo].[tblCASESTATUS]
FOR INSERT, UPDATE
AS
BEGIN
	DECLARE 
		@CaseID int,
		@Cancel bit
	SELECT @CaseID = CaseID, @Cancel = Cancel FROM inserted
	UPDATE [dbo].[tblCase] SET Cancel = @Cancel WHERE CaseID = @CaseID
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
In both triggers, use

Update... Set... Where...
AND cancel is null or cancel <> @cancel

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial