SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trigger Problems, Transaction context in use by another session


Trigger Problems, Transaction context in use by another session

Author
Message
LunaFortuna
LunaFortuna
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
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



Matija Lah
Matija Lah
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2995 Visits: 380
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search