January 19, 2009 at 5:21 am
One of our developers has come to me with an error with a trigger that it quite simply over my head.
The below code is a trigger on a table, which runs at 'insert'. The 'SAVE TRAN' at the start of the outer loop should save the start point of the current transaction but gives the following error:
Msg 3931, Level 16, State 1, Procedure TRIGGER_R_T_XREF_REGIONCODE, Line 53
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
I have tried moving the SAVE into the TRY block but get the same error. I have inserted the code below, please could you let me know if there is any clear logical error or you have any suggestions.
Thanks in advance.
USE [MYDB]
GO
/****** Object: Trigger [dbo].[tr_XTEST_DEL] Script Date: 01/15/2009 15:00:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRIGGER_R_T_XREF_REGIONCODE]
ON [dbo].[T_XREF_REGIONCODE]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SAVE TRANSACTION PreRepositoryLogging ; -- so that we can preserve the triggering event if we abandon the trigger actions
SET NOCOUNT ON; -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
BEGIN TRY
DECLARE @D as char(1)
set @D = isnull((select top(1) 'U' from INSERTED),'D')
DECLARE @I as char(1)
set @I = isnull((select top(1) 'U' from DELETED),'I')
-- delete --------------------------------------------------------------
INSERT INTO dbo.R_T_XREF_REGIONCODE
SELECT
--[repository_rowid] bigint identity not null,
getdate() as [repository_load_datetime] ,
getdate() as [repository_end_date] ,
'-' as [repository_current_record] ,
getdate() as [repository_last_update_datetime] ,
@D as [repository_last_update_type] ,
'1' as [repository_version_no] ,
DELETED.*
FROM DELETED
-- insert --------------------------------------------------------------
INSERT INTO dbo.R_T_XREF_REGIONCODE
SELECT
--[repository_rowid] bigint identity not null,
--getdate() as [repository_load_datetime] ,
'x' as [repository_load_datetime] , -- deliberately bad to test error handling
null as [repository_end_date] ,
'-' as [repository_current_record] ,
getdate() as [repository_last_update_datetime] ,
@I as [repository_last_update_type] ,
'1' as [repository_version_no] ,
INSERTED.*
FROM INSERTED
END TRY
BEGIN CATCH
-- what to do with an error is still to be defined,
ROLLBACK TRANSACTION PreRepositoryLogging ; -- roll back to beginning of trigger
END CATCH
END
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 19, 2009 at 7:08 am
Based on what I have read in BOL, you need to have an explicit COMMIT TRANSACTION.
A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. If a transaction is rolled back to a savepoint, it must proceed to completion with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement, or it must be canceled altogether by rolling the transaction back to its beginning.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 19, 2009 at 7:10 am
Hey Jack,
Hoped this would reach you. Will pass on to the developer and get him test.
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 19, 2009 at 7:19 am
Jack,
Where should the COMMIT in the code be? the developer reckons at the start of the trigger (before the SAVE), but I thought at the bottom of the TRY.
Are either of us right?
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 19, 2009 at 7:48 am
I don’t think that a commit statement is missing. I think that if there is a runtime error in a trigger, the whole transaction has to be rolled back. Check my code that shows this behavior.
use tempdb
go
create table MyTable (i int)
go
--Creating a table that will
--always rollback to the save point.
create trigger MyTrigg
on MyTable
for insert
as
begin
SAVE TRANSACTION t1
update MyTable set i = i*3
ROLLBACK TRANSACTION t1
end
go
--You can see that the trigger
--worked and that the trancount is zero
--without writing an extra commit
INSERT INTO MyTable (i) values (1)
select @@trancount, * from MyTable
go
--modify the trigger so there
--will be an error.
alter trigger MyTrigg
on MyTable
for insert
as
begin
begin try
SAVE TRANSACTION t1
update MyTable set i = i*3
raiserror ('Test error',16,1)
end try
begin catch
ROLLBACK TRANSACTION t1
end catch
end
go
--since there is an error in the trigger
--and I'm trying to rollback to the
--save point, it doesn't work. the error
--message says that I have to completly
--rollback the entire transaction.
INSERT INTO MyTable (i) values (1)
select * from MyTable
go
--clean up
drop table MyTable
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 19, 2009 at 8:36 am
Take a look at the SAVE TRANSACTION entry in BOL, it has a pretty good example.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 20, 2009 at 2:38 am
Hey All,
Just to update you, we have found a way around this issue. Turns out we dont need to role back single inserts within the trigger.
We tried all we could think of and it just wouldnt work. Eventually I have come to the conclusion that the SAVEPOINT construct just doesnt work within a trigger (though feel free to correct me if you have an example of it working).
HTH
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply