Transaction and errors

• JulieW

  SSChasing Mays

  Points: 600

  More actions

  November 5, 2003 at 3:46 am

  #101743

  Dear All

  I was wondering if an expert could have a look at this to see what is wrong.

  I am trying to get a rollback to work, do it I am purposely putting in an error then attempting a rollback. What is happening is that all the non unique records are being inserted, when the should of been rolled back.

  create table tblTempTableTwo

  (

  ID int not null PRIMARY KEY,

  col1 int

  )

  BEGIN TRANSACTION

  INSERT INTO tblTempTableTwo values (2, 1)

  if @@ERROR <> 0 GOTO TRAN_ABORT

  INSERT INTO tblTempTableTwo values (3, 56)

  if @@ERROR <> 0 GOTO TRAN_ABORT

  INSERT INTO tblTempTableTwo values (3, 56)

  commit transaction

  goto FINISH

  TRAN_ABORT:

  ROLLBACK TRANSACTION

  FINISH:

  GO

  SELECT * FROM tblTempTableTwo

  GO

  RESULTS...

  (1 row(s) affected)

  (1 row(s) affected)

  Server: Msg 2627, Level 14, State 1, Line 1

  Violation of PRIMARY KEY constraint 'PK_tblTempTableTwo'. Cannot insert duplicate key in object 'tblTempTableTwo'.

  The statement has been terminated.

  (2 row(s) affected)

  Thanks

  J

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  November 5, 2003 at 3:55 am

  #480851

  You've missed out the "if @@ERROR <> 0 GOTO TRAN_ABORT" check after the last insert.

• JulieW

  SSChasing Mays

  Points: 600

  More actions

  November 5, 2003 at 4:09 am

  #480852

  I think the term 'DUH' comes to mind, sorry for wasting your time, and thanks.

  J