BEGIN TRANSACTION COMMIT TRANSACTION help

I had thought that if any statement failed within a BEING TRANS .. COMMIT TRANS block, then all the statements would be rolled back. But I am seeing different behavior (SQL Server 2000 8.00.2039)

For instance, run these statements to set up a test:
--DROP TABLE testTable1
--DROP TABLE testTable2
CREATE TABLE testTable1 (f1 varchar(1))
CREATE TABLE testTable2 (f1 varchar(1))
CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1)
insert into testTable1(f1) values ('a')

So table testTable1 has a unique index on it..

Now try to run these statements:

--DELETE FROM testTable2
BEGIN TRANSACTION
insert into testTable1(f1) values ('a')
insert into testTable2(f1) values ('a')
COMMIT TRANSACTION

SELECT * FROM testTable2

..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?in order to roll back a transaction, you have to specifically issue the ROLLBACK command. usually after checking the error status of each operation that matters.|||Huh. That sort of stinks. I had thought an error that occurred within an explicit transaction would automatically rollback the transaction. Guess not...

So, I have modified the test as follows and it works:

DECLARE @.errorHolder int
SELECT @.errorHolder = 0
--DELETE FROM testTable2
BEGIN TRANSACTION aaa
insert into testTable1(f1) values ('a')
SELECT @.errorHolder = @.errorHolder + @.@.ERROR
insert into testTable2(f1) values ('a')
SELECT @.errorHolder = @.errorHolder + @.@.ERROR
IF @.errorHolder > 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SELECT * FROM testTable2

Is there an easier way|||Use
SET XACT_ABORT ON
to automatically roll back a transaction if a run-time error is raised in the TSQL statements.|||thanks kaffenils, that's much easier.|||I prefer to try and catch my errors withg error handling and then I have control over my logic

BUT DDL?|||I prefer to try and catch my errors withg error handling and then I have control over my logic

I agree that TRY...CATCH is preferrable in SQL Server 2005 as it gives you more control, but craigmc is using SQL Server 2000. It is time consuming work to use @.@.ERROR if the only purpose is to rollback the transaction. XACT_ABORT could provide a qiuick and dirty solution if handling an error only means rolling back the transaction.|||Time consuming? That's your justification?!
Oh dear...|||Time consuming? That's your justification?!
Oh dear...

Did you even read what I wrote? Have I said not to use TRY...CATCH or @.@.ERROR if you need more control of exceptions?
"Oh Dear" :shocked:

All I said was that if you have multiple DML statements (and you're running on SQL Server 2000) and all you need to do if one of them fails is rollback, then it is easier to SET XACT_ABORT ON than to writing multiple
IF @.@.ERROR<>0 BEGIN ... END or [GOTO abc
On SQL Server 2005 I would always use TRY...CATCH, except for my own simple one-time ad-hoc DMLs. Then XACT_ABORT will do the job good enough.