SQL Server Performance

Does rollback work in Sql server for delete and update

Discussion in 'SQL Server 2005 General Developer Questions' started by Varsha, May 1, 2008.

  1. Varsha New Member

    If i update or delete some set of record then how do i rollback the changes. Is there any query for it. for e.g. if i run the following query:
    Update salesproposal set statuscode = 4 where so_id = 47888
    How do i rollback this query in SQL Server.
    Thanks for your responses!!!
    Varsha, May 1, 2008
    #2

  2. samsqlserver New Member

    Rollback does work for DML(insert,update,delete).
    The rollback shud have a "begin tran"
    Try this:
    begin tran test
    Update salesproposal set statuscode = 4 where so_id = 47888
    /* at this point if u do a select om ur table salesproposal u shud see the changes
    now u can either do a "commit tran" or "rollback tran" . If u do a commit changes will be made permanent or if rollback changes will be rolled back */
    rollback tran test
    /* now ur select statement will give u the result that was before the update statement.
    Hope this answrd ur question. Let me now if anything.
    samsqlserver, May 1, 2008
    #3

  3. subramanivg New Member

    Hi Sam,
    The above said rollback works only once irrespetive of earlier queries exectuted with begintran.
    For Ex:- If I run 10 Queries in different time intervals using Begin Tran test, I cannot run the roll back test for those queries in different time intervals. If I rollback First query out of 10 queries, it works. Next when I try for second query, it gives message
    "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."
    Is there any solution for this. I am using SQL2000
    subramanivg, May 1, 2008
    #4

  4. satya Moderator

    You need be more clear on nested transactions then, say with the BEGIN TRANSACTION statement increments the @@TRANCOUNT Server Variable with 1, and the COMMIT TRANSACTION decrements the @@TRANCOUNT variable with 1.
    The ROLLBACK TRAN statement however, decrements the @@TRANCOUNT server variable to 0. That's why the last COMMIT statement gives us the error message: there has been a rollback, and therefore the @@Trancount is set to zero.
    Apparently, the ROLLBACK TRANSACTION also rollbacks to the most outer begin transaction, it is not possible to use nested transactions in this way.
    The way out is to use SAVE POINTS, you start a transaction, execute a statement, and save the transaction using the SAVE TRANSACTION sp1 statement. This statement sets a savepoint with the name 'sp1'. You can then rollback to that savepoint using the ROLLBACK TRAN <savepointname> command.
    Further information on nested transactions can be obtained from updated books online.
    satya, May 2, 2008
    #5

  5. FrankKalis Moderator

    [quote user="subramanivg"]
    Hi Sam,
    The above said rollback works only once irrespetive of earlier queries exectuted with begintran.
    For Ex:- If I run 10 Queries in different time intervals using Begin Tran test, I cannot run the roll back test for those queries in different time intervals. If I rollback First query out of 10 queries, it works. Next when I try for second query, it gives message
    "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."
    Is there any solution for this. I am using SQL2000
    [/quote]
    Nested transactions is one of the thing I yet have to find a valid reason for. In most cases you don't need this as you can check @@TRANCOUNT if you are already in a transaction or not. And if you are, then there is no need to start another one. This will also save you from these error you experience now.
    FrankKalis, May 5, 2008
    #6

Share This Page