Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Unanswered: doubt in rollback transactions

    may be an idiotic question

    I have created a table test1 with primary key as given below. I have written a procedure to insert rows. is the rollback transaction given under is correct?. (OR) shall i give the rollback only once at the end?

    need more explanation on the rollback transaction.



    CREATE TABLE [TEST1] (
    [COL1] [varchar] (50) NOT NULL
    ) ON [PRIMARY]
    GO


    ALTER TABLE [TEST1] WITH NOCHECK ADD
    CONSTRAINT [PK_TEST1] PRIMARY KEY CLUSTERED
    (
    [COL1]
    ) ON [PRIMARY]
    GO


    ALTER PROCEDURE T AS
    BEGIN TRANSACTION
    INSERT INTO TEST1 VALUES('A')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('B')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('B')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('C')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('D')
    IF (@@ERROR <> 0) GOTO ERR
    ERR:
    IF (@@ERROR <> 0)
    ROLLBACK TRANSACTION
    else
    COMMIT TRANSACTION

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm big on a rather deviant code construct, that I think I might have invented ages ago. It uses a peculiar arrangement to make a successful transaction become two transactions and a failed transaction become one. The logic goes something like:
    Code:
    CREATE PROCEDURE dbo.pDemo
    AS
    
    BEGIN TRANSACTION  -- Start "real" transaction
    
    INSERT INTO TEST1 VALUES('A')
    IF (@@ERROR <> 0) GOTO ERR
    
    INSERT INTO TEST1 VALUES('B')
    IF (@@ERROR <> 0) GOTO ERR
    
    INSERT INTO TEST1 VALUES('C')
    IF (@@ERROR <> 0) GOTO ERR
    
    COMMIT TRANSACTION  --  Commit the working transaction if we made it this far
    BEGIN TRANSACTION  -- Start dummy to keep matches
    
       ERR:
    ROLLBACK TRANSACTION  --  Rollback whichever transaction is open now
    
    RETURN
    GO
    This code is deviant, but it has worked very well over the years for me.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    80

    Re: doubt in rollback transactions

    Try this
    BEGIN TRANSACTION
    INSERT INTO TEST1 VALUES('A')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('B')
    IF (@@ERROR <> 0) GOTO ERR
    --INSERT INTO TEST1 VALUES('B')
    --IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('C')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('D')
    IF (@@ERROR <> 0) GOTO ERR
    COMMIT TRANSACTION
    GOTO FINALLY
    ERR:
    ROLLBACK TRANSACTION

    FINALLY:
    --THAT'S IT FOR NOW


    I think there is nothing syntatically wrong with what u have writen.
    but think about the logic.I guess u are considering all the insert stmts
    as a single transaction.
    did u find that second and third insert are giving the same value for a primary key column.


    Originally posted by jtamil2001
    may be an idiotic question

    I have created a table test1 with primary key as given below. I have written a procedure to insert rows. is the rollback transaction given under is correct?. (OR) shall i give the rollback only once at the end?

    need more explanation on the rollback transaction.



    CREATE TABLE [TEST1] (
    [COL1] [varchar] (50) NOT NULL
    ) ON [PRIMARY]
    GO


    ALTER TABLE [TEST1] WITH NOCHECK ADD
    CONSTRAINT [PK_TEST1] PRIMARY KEY CLUSTERED
    (
    [COL1]
    ) ON [PRIMARY]
    GO


    ALTER PROCEDURE T AS
    BEGIN TRANSACTION
    INSERT INTO TEST1 VALUES('A')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('B')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('B')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('C')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('D')
    IF (@@ERROR <> 0) GOTO ERR
    ERR:
    IF (@@ERROR <> 0)
    ROLLBACK TRANSACTION
    else
    COMMIT TRANSACTION
    Last edited by theguru; 03-11-04 at 02:44.

  4. #4
    Join Date
    Jan 2003
    Posts
    41

    Re: doubt in rollback transactions

    Guru
    I want to know about rollback transaction and I purposely gave two insert statement with same value, so that the transaction got rollback. Is this the only way to commit and rollback?

    I want like, if i got deleted all should get deleted or else all should get inserted. so i have to give the if condition for each and every insert or delete statement?

    Thanks



    Originally posted by theguru
    Try this
    BEGIN TRANSACTION
    INSERT INTO TEST1 VALUES('A')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('B')
    IF (@@ERROR <> 0) GOTO ERR
    --INSERT INTO TEST1 VALUES('B')
    --IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('C')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('D')
    IF (@@ERROR <> 0) GOTO ERR
    COMMIT TRANSACTION
    GOTO FINALLY
    ERR:
    ROLLBACK TRANSACTION

    FINALLY:
    --THAT'S IT FOR NOW


    I think there is nothing syntatically wrong with what u have writen.
    but think about the logic.I guess u are considering all the insert stmts
    as a single transaction.
    did u find that second and third insert are giving the same value for a primary key column.

  5. #5
    Join Date
    Mar 2004
    Posts
    80

    Re: doubt in rollback transactions

    thats fine,
    @@ERROR returns the number of the error message until another T-SQL statement is executed.
    I think u r checking @@ERROR<>0 condition in ERR: block which u have allready checked after each insert statement.
    @@ERROR in err: block returns 0 since there is no error in its previous if statement.isn't it?

    ----------------------------------------
    ALTER PROCEDURE T AS
    BEGIN TRANSACTION
    INSERT INTO TEST1 VALUES('A')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('B')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('B')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('C')
    IF (@@ERROR <> 0) GOTO ERR
    INSERT INTO TEST1 VALUES('D')
    IF (@@ERROR <> 0) GOTO ERR
    ERR:
    IF (@@ERROR <> 0)
    ROLLBACK TRANSACTION
    else
    COMMIT TRANSACTION
    Last edited by theguru; 03-11-04 at 04:49.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Give both pieces of code a try, see which one you like better in terms of its behavior.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •