Geek Answers Handbook

Rollback of the entire procedure (all statements)

How can I write a procedure so that I can rollback all the INSERT, UPDATE, and DELETE statements whenever the ANY statement has an error.

Please note that my procedure may not have the operators listed in the sequence. In other words, I have INSERT statements, then IF logic, select statement, then another INSERT, then UPDATE, followed by logic, DELETE statement, etc.

I just want ROLLBACK all INSERT, UPDATE and DELETE statements if an error occurred for any statement. I found this code http://msdn.microsoft.com/en-us/library/ms181299.aspx and http://en.allexperts.com/q/MS-SQL-Server-1801/Rollback-SP.htm

But they do not answer my question.

+5
source share
3 answers

If a transaction is executed with the XACT_ABORT parameter , this will happen automatically.

set xact_abort on
begin tran

/*Your code*/


commit

Here's a pretty interesting question discussing the use of this constructive error handling.

+4
source

Find out how the transaction works (the link you give also uses it, but the transaction seems distorted due to DTS interaction). Basically, you can roll back everything you did, since you indicated that you were starting a transaction.

Martin Smith's answer using xact_abort is described in more detail here .

+2
source

, , " ". , " " .

, , , .

. .

Begin transaction
    Update Table One
    Update table Two
    [Update lots of tables]
    if all updates successfull
        commit (All changes to all tables)
    else
        rollback (All changes)
End transaction

, "", "" "" , .

, , . http://www.subbu.org/articles/nuts-and-bolts-of-transaction-processing

DB, .

Hth if not to comment and i will try again. :-)

+2
source

More articles:


All Articles