October 17, 2007 at 4:44 am
Iam using Sqlserver 2005 express edition, I have set the below trigger in one of the table. Iam getting an error "Could not continue scan with Nolock due to data monvement" when I tried to open it from Sqlserver studio.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [ust_Dial]
ON [dbo].[History]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
Declare @xPhNo as varchar(15), @xCRC as varchar(5) ,@xDialId as int, @isAlt as int, @xCBDateTime as datetime
Declare @nCallFlag as tinyint
Declare @cSuccess as varchar(5),@cRecall as bit, @cFinalCrC as bit
SELECT @xPhNo=PhoneNum,@xCRC=CRC,@xDialId=dialid, @isAlt=isAlt, @xCBDateTime=CallDateTimeFROM INSERTED
Select @cSuccess=success,@cRecall=recall,@cFinalCrC=finalCRC
from CRC where CRC=@xCRC
IF @cRecall=1
BEGIN
IF (@isAlt=1)
BEGIN
Update Dial set CBDateTime=@xCBDateTime where DialId=@xDialId
Update DialAlt set CBDateTime=@xCBDateTime where DialId=@xDialId and PhoneNum<>@xPhNo
END
ELSE
BEGIN
Update DialAlt set CBDateTime=@xCBDateTime where DialId=@xDialId
END
END
IF @cFinalCrC=1
BEGIN
IF (@isAlt=1)
BEGIN
Update Dial set CallFlag=0 where DialId=@xDialID
Update DialAlt set CallFlag=0 where DialId=@xDialID
END
ELSE
BEGIN
Update DialAlt set CallFlag=0 where DialId=@xDialID
END
DELETE FROM Callback where dialid=@xDialID
END
END
October 17, 2007 at 6:01 am
This is something that can happen in read committed isolation level. Have a look at http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx.
Normally you can get around it if you impose some more restrictions on the transactions by changing to repeatable read isolation level.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Do have a look at the statements that are running at the moment you are getting this error though to see if you could just avoid this problem.
Regards,
Andras
October 17, 2007 at 6:13 am
(NOLOCK) hints are the sign of despair, usually they come from SQL 2000/7
On SQL 2005 you can use new isolation modes, like SNAPSHOT isolation
October 17, 2007 at 7:51 am
I addition you should code your trigger to be "multi-row" aware always.
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply