Link to home
Start Free TrialLog in
Avatar of jdana
jdanaFlag for United States of America

asked on

Conflicting Triggers - Looking for a workaround

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

ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jdana

ASKER

Thanks!