April 23, 2008 at 1:06 am
Hello,
please bare with me as I am new to this. We have two SQL 2005 Database Systems. After an update at a Table in Database A, a trigger is fired which tries to update a datarow in one of the tables on Database B. On the table in Database B also a "After Update" Trigger runs, which only should react when a specific value is in the table of Database A. For this I have insert a select statement (see below bold) to the other table in the trigger. Now I always get the error: Transaction context in use by another system. If I uncomment the select statement (see below bold) it seems to work, but I can't compare my values then. How can I avoid this?
Thank you for your time.
Best Regards
Luna
:w00t:
Database A Trigger:
USE [TransferDb]
GO
/****** Object: Trigger [dbo].[Trigger_externUpdate_TT_Aux] Script Date: 04/23/2008 07:32:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trigger_externUpdate_TT_Aux] ON [dbo].[TT_Aux]
AFTER UPDATE AS
Declare @RecId int, @AccessFlag int
BEGIN
select @RecId = i.[RecId], @AccessFlag = i.[AccessFlag] from INSERTED as i JOIN DELETED as d ON i.RecId = d.RecId
Set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @AccessFlag < 4
UPDATE [Server1].[TransferDB].[dbo].[TT_Aux] SET [AccessFlag] = @AccessFlag WHERE [RecID] = @RecId
END
Database B Trigger:
USE [TransferDb]
GO
/****** Object: Trigger [dbo].[INS_TT_Aux_3] Script Date: 04/23/2008 07:35:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*** Trigger instead of update to make the error handling ***/
ALTER TRIGGER [dbo].[INS_TT_Aux_3]
ON [dbo].[TT_Aux]
AFTER UPDATE
AS
Declare
@RecId int,
@access_flag int,
@pps_access_flag int,
@l_access_flag int,
@query nvarchar(256)
BEGIN
--Get all the access flags and the RecId of the last updated record
Select @RecId = d.RecId, @l_access_flag = d.AccessFlag
from INSERTED AS i JOIN DELETED AS d on i.RecId = d.RecId
set @access_flag = (select AccessFlag from TT_Aux where RecId = @RecId)
set @pps_access_flag = (select AccessFlag from [Server1\PPS_SQL].[TransferDb].[dbo].[TT_Aux] where RecId = @RecId)
if (@pps_access_flag = 3 AND @access_flag = 4)
begin
UPDATE TT_Auxiliary SET AccessFlag = @l_access_flag where RecId = @RecId
end
else
begin
if (@pps_access_flag < 3 AND @access_flag = 4)
begin
UPDATE [Server1\PPS_SQL].[TransferDb].[dbo].[TT_Aux]
SET
[AccessFlag] = @access_flag
WHERE
RecId = @RecId
end
else
begin
UPDATE TT_Aux SET AccessFlag = @access_flag where RecId = @RecId
end
end
END
May 21, 2008 at 2:35 am
This is the expected behaviour - you run into a deadlock.
But most importantly, you're neglecting the fact that triggers fire per statement, not per row, so the ootcome of this statement:
select @RecId = i.[RecId], @AccessFlag = i.[AccessFlag] from INSERTED as i JOIN DELETED as d ON i.RecId = d.RecId
...is completely unpredictable.
Normally, one would handle situations like this in the stored procedure used to write to table A; namely, decide up front whether (and how) the data must be changed in table B.
If, however, for some reason you think you must be using triggers, then use the logic in the first trigger to decide whether the data should be modified in table B, rather than letting that decision fall on a trigger on table B. After all, it is table A that holds the data necessary for this decision, doesn't it?
The definition of the trigger on table A should read somewhere along these lines:
update
set
...
,
from inserted
where
and
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply