jdana
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_tblCASEStatusReplicateC ancel runs, which makes TR_tblCaseReplicateCancel want to run again...
Can anyone think of a clever workaround to prevent this iterative effect?
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_tblCASEStatusReplicateC
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER