Transaction Query - Not rolling back on step 1

[sreemati]

Hi Everyone,

In brief, I am trying to get the whole block of code done in one go or not to do at all.

Here is a piece of code I am trying:
[sql]
BEGIN TRANSACTION

INSERT INTO authors (au_id, au_lname, au_fname, phone, contract)
VALUES ('172-32-1176', 'Mickey', 'Mouse', '222-33-4444', 1)

IF @@ERROR <>0
BEGIN
ROLLBACK TRAN
PRINT 'STEP 1 DONE'
print @@ERROR
END

UPDATE authors
SET au_fname = 'pickey'
WHERE au_id = '172-32-1176'

IF @@ERROR <>0
BEGIN
ROLLBACK TRAN
PRINT 'STEP 2 DONE'
print @@ERROR
END

COMMIT TRANSACTION
[/sql]

Though it shows primary key constraint and not doing the insert, it still moves to update.

I want it to insert and than update or not do anything at all - means if it can not insert, than end rather than do update.

Please help. I am sure I am missing something very obvious but can not figure out what.

Thanks a lot for your help in advance.
Sree

[ck9663]

Hi Everyone,

In brief, I am trying to get the whole block of code done in one go or not to do at all.

Here is a piece of code I am trying:
[sql]
BEGIN TRANSACTION

INSERT INTO authors (au_id, au_lname, au_fname, phone, contract)
VALUES ('172-32-1176', 'Mickey', 'Mouse', '222-33-4444', 1)

IF @@ERROR <>0
BEGIN
ROLLBACK TRAN
PRINT 'STEP 1 DONE'
print @@ERROR
END

UPDATE authors
SET au_fname = 'pickey'
WHERE au_id = '172-32-1176'

IF @@ERROR <>0
BEGIN
ROLLBACK TRAN
PRINT 'STEP 2 DONE'
print @@ERROR
END

COMMIT TRANSACTION
[/sql]

Though it shows primary key constraint and not doing the insert, it still moves to update.

I want it to insert and than update or not do anything at all - means if it can not insert, than end rather than do update.

Please help. I am sure I am missing something very obvious but can not figure out what.

Thanks a lot for your help in advance.
Sree

i think @@error will reset after each successful statement so you have to get the value before you execute a next statement. i believe a trigger will be best use in thise case