Transaction Logic. argh. - 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 2000 Forums

SQL Server Development (2000)

Transaction Logic. argh.

Author

Topic

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-07 : 08:15:35

Hmm. I am curious as to what is wrong with my transaction logic here..

Take for example this




IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)


create table moo (moo int null)

SET XACT_ABORT ON
GO

BEGIN TRAN
go

INSERT INTO moo values ('eggcup') -- This produces an error
go

INSERT INTO moo values (1)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0
BEGIN
ROLLBACK TRANSACTION
END
GO

IF @@TRANCOUNT=0 
BEGIN
INSERT INTO #tmpErrors (Error) SELECT 1
PRINT 'FAILED'
BEGIN TRANSACTION
END
ELSE: PRINT 'SUCCESS'
GO


IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
Print 'succeeded'
COMMIT TRANSACTION
End
Else: Print 'failed'
GO
DROP TABLE #tmpErrors
GO

select * from moo
drop table moo

Now, what I would like to see here, is that select * from moo returning 0 rows. But, instead, it returns the 1 from the row that was INSERTed before my ROLLBACK TRANSACTION.

So, my question is, why does my ROLLBACK not do so?

One possible assumption is that it is the GO statement causing this not to happen, but, if that is the case, then why does this work..


create table moo (moo int null)
begin tran
go
insert into moo values (1)
go
ROLLBACK
select * from moo
drop table moo

Essentially, what I am trying to achieve is that if *any* statement within the transaction produces an error, I want the transaction to stop executing and roll back.

-------
Moo. :)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-07-07 : 08:27:46

mist, mate...you have got to trap and check the value of the @@ERROR variable after every statement that could potentially fail. It's even safer to store the value of @@ERROR into a local variable, since it is reset after every statement - it really is quite volatile and disappears rather quickly. So your inserts would have to be like this:


INSERT INTO #moo VALUES('doodle doo')
SELECT @MyError = @@ERROR

IF @MyError > 0 
BEGIN
  RAISE all hell
END

INSERT INTO #moo VALUES(2)
SELECT @MyError = @@ERROR

IF @MyError > 0 
BEGIN
  RAISE more hell
END

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-07 : 08:45:31

Hmm. I feared as much. Sadly the script that this problem actually relates to is a lot longer than my example. :|

Is there no way to get the transaction to bomb out after any error?

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources