August 31, 2010 at 2:16 am
Hi,
In a transaction there are 3 update statements. The 2nd update statement invokes a trigger which in turn updates a audit table.
The 3rd update fails which rolls back the transaction. So, will the record inserted by the trigger will also be rolled back?
Regards,
Suraj
August 31, 2010 at 8:03 am
Have you tested this? What did you find out?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2010 at 8:22 am
suraj.app (8/31/2010)
Hi,In a transaction there are 3 update statements. The 2nd update statement invokes a trigger which in turn updates a audit table.
The 3rd update fails which rolls back the transaction. So, will the record inserted by the trigger will also be rolled back?
Regards,
Suraj
DECLARE @TABLE AS TABLE(
col1 VARCHAR(5))
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO @TABLE
SELECT '1'
COMMIT TRAN
SELECT * FROM @TABLE
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
END CATCH;
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO @TABLE
SELECT '2'
COMMIT TRAN
SELECT * FROM @TABLE
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
END CATCH;
BEGIN TRY
BEGIN TRANSACTION
SELECT 1/0
INSERT INTO @TABLE
SELECT '3'
COMMIT TRAN
SELECT * FROM @TABLE
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
END CATCH;
SELECT * FROM @TABLE
*not at my desk, so syntax there might be off. Hopefully you'll get the general idea though
WayneS (8/31/2010)
skcadavre,This isn't really a good example, since table variables don't participate in transactions to begin with.
Apologies, wasn't at my desk until this morning and I see you're right. Thanks for letting me know.
August 31, 2010 at 9:37 am
skcadavre,
This isn't really a good example, since table variables don't participate in transactions to begin with.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2010 at 10:03 am
I tried with the below code and got the result. Even the table updated by trigger will be rolled back.
BEGIN TRAN
DECLARE @INTERRORCODE INT
INSERT INTO BOOKS(BOOKID,BOOKDESCRIPTION,ISBN) VALUES(2,'22','222')
INSERT INTO BOOKS(BOOKID,ISBN) VALUES(2,'222')
SELECT @INTERRORCODE = @@ERROR
IF (@INTERRORCODE <> 0) GOTO PROBLEM
COMMIT TRAN
PROBLEM:
IF (@INTERRORCODE <> 0) BEGIN
PRINT 'UNEXPECTED ERROR OCCURRED!'
ROLLBACK TRAN
END
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy