Tran statement help - 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 2008 Forums

Transact-SQL (2008)

Tran statement help

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-11-01 : 11:00:47

Hi,

I need some help on this simple task.

The logic is this. Only if the INSERT succeeded, DELETE will take place.

So, I have this:

BEGIN Tran t1
Insert into ABC Select ...
Delete xyz
GO
COMMIT Tran t1
GO

My test indicated half of the logic worked, i.e. when insert runs, delete also runs.
However, when I intentional failed the insert, by trying to get Null into NOT NULL field, the DELETE neverthless still runs.

BOL said setting XACT_ABORT to ON is for this, but I couldn't pull it off.

Help and THANKS!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-01 : 11:12:11

You should "handle" the failure somewhere with an explicit ROLLBACK. Either in the catch a Try/Catch block or just with a conditional test for success/failure.

Be One with the Optimizer
TG

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-01 : 11:16:18

How did you use XACT_ABORT? The following should bypass the delete in the example you described

SET XACT_ABORT ON ;
BEGIN Tran t1
Insert into ABC Select ... 
Delete xyz
GO
COMMIT Tran t1
GO

There is a pretty good write-up here about batch terminating errors, and XACT_ABORT:
http://www.sommarskog.se/error-handling-I.html

Also you may want to consider using TRY-CATCH

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-11-01 : 11:51:06

I have it exactly like yours.

I got two messages.

First is the expected Cannot insert the value NULL.

Second
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

and, even it said 0 row affected, the delete did happen.

quote:
Originally posted by sunitabeck

How did you use XACT_ABORT? The following should bypass the delete in the example you described
SET XACT_ABORT ON ;
BEGIN Tran t1
Insert into ABC Select ...
Delete xyz
GO
COMMIT Tran t1
GO

There is a pretty good write-up here about batch terminating errors, and XACT_ABORT:
http://www.sommarskog.se/error-handling-I.html

Also you may want to consider using TRY-CATCH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-01 : 13:24:09

quote:
Originally posted by Hommer

I have it exactly like yours.

I got two messages.

First is the expected Cannot insert the value NULL.

Second
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

and, even it said 0 row affected, the delete did happen.

quote:
Originally posted by sunitabeck

How did you use XACT_ABORT? The following should bypass the delete in the example you described
SET XACT_ABORT ON ;
BEGIN Tran t1
Insert into ABC Select ...
Delete xyz
GO
COMMIT Tran t1
GO

There is a pretty good write-up here about batch terminating errors, and XACT_ABORT:
http://www.sommarskog.se/error-handling-I.html

Also you may want to consider using TRY-CATCH

That's due to the batch terminator (GO). Remove that and you should be fine.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-11-01 : 14:09:38

Thank you for pointing that out.

But it seems rmoving those two GOs did not preventing DELETE.

I am going to try the TRY CATCH route...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-01 : 14:18:40

I must be missing something in your description. I tried the following code, and it did not delete data from table A2, which is what I would have expected. When you have XACT_ABORT on, it terminates the batch, so the delete does not happen.

 -- create the tables.
CREATE TABLE dbo.A2(id INT);
INSERT INTO dbo.A2 VALUES (1);

-- test
--------------------------------
SET XACT_ABORT ON
BEGIN TRAN t1
INSERT INTO dbo.A1 VALUES (NULL);
DELETE dbo.A2;
GO
COMMIT Tran t1
GO

-- result
-------------------------------
Msg 515, Level 16, State 2, Line 8
Cannot insert the value NULL into column 'id', table 'MyDb.dbo.A1'; column does not allow nulls. INSERT fails.
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
-------------------------------

-- is data still in the table?
SELECT COUNT(*) FROM dbo.A2; -- this shows 1, so it did not delete the data from A2.
GO

-- cleanup
DROP TABLE dbo.A1;
DROP TABLE dbo.A2;
GO

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-11-01 : 14:48:36

Well, I followed BOL sample and have this:
BEGIN TRY
BEGIN TRAN t1
--run insert
INSERT INTO...
--run Delete
DELETE...
COMMIT TRAN t1;
END TRY
BEGIN CATCH --if any error, don't do anything.
ROLLBACK TRAN t1;
END CATCH;
GO

I got
Msg 6401, Level 16, State 1, Line 150
Cannot roll back t1. No transaction or savepoint of that name was found.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-01 : 15:34:26

Usually I follow the example on this MSDN page - example "C. Using TRY…CATCH with XACT_STATE". You use XACT_STATE to determine what to do - whether to do nothing at all, rollback or commit. In my experience that works as advertised: http://msdn.microsoft.com/en-us/library/ms175976.aspx

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-11-01 : 16:12:20

OK, I got the SET XACT_ABORT ON worked.

I guess my testing code has some flaws in it...

Thanks every one!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources