Transaction Not Rolling Back - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Transaction Not Rolling Back

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2007-05-13 : 12:09:35

I have this...


CREATE TRIGGER [trig_tbl_newspaper_download_delete_files] ON [dbo].[tbl_newspaper_download] 
FOR DELETE 
AS

SET XACT_ABORT ON

RAISERROR ('An error occured updating the tabke',16,1)

INSERT INTO tbl_FileToDelete SELECT '/' + FileName, 'DownloadsDirectory' FROM deleted

GO

The app receives an exception but the initial delete is not rolled back.

Why isn't the delete trigger included in the same transaction as the delete?

Cheers, XF

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-13 : 12:27:01

Having just RAISERROR() inside a trigger is not sufficient to rollback the transaction. You have to explicitly write ROLLBACK TRANSACTION statement inside the trigger.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-13 : 12:37:16

beware that rolling back a transaction will also empty your deleted pseudo table
so you won't be able to insert anything with your insert.

you must use this technique:
http://weblogs.sqlteam.com/mladenp/archive/2007/04/13/60174.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2007-05-13 : 13:03:30

The RAISEERROR was just to simulate an error with the insert. I tried instead changing the name of tbl_FileToDelete which caused an exception and a rollback without even setting XACT_ABORT. So that's what I was after!

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources