Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger Problems, Transaction context in use by another session Expand / Collapse
Author
Message
Posted Wednesday, April 23, 2008 1:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 2:29 AM
Points: 1, Visits: 19
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


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


Post #489076
Posted Wednesday, May 21, 2008 2:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 28, 2013 10:48 AM
Points: 2,869, Visits: 371
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
Post #504278
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse