Transaction Processing

[jmeadows7]

I am importing data into a staging table and perform several validation checks against the data in the stored procedure prior to updating the production tables. If any logical errors are encountered, the update statements do not process. However, if the update statements do start to process, I want them all to work or none. Therefore, if one abends, I want to roll back all updates. Can someome please provide me the correct syntax for catching ensuring this happens. The pseudo code for what I want to do is below. If an update statement blows up and the stored procedure abends, do all of the update statements get rolled back if you have a transaction defined?

Thanks,


check errors

If no errors
begin transaction
peform update 1
perform update 2
commit transaction
end if

if error - roll back all updates.

 

[bborissov]

DECLARE @ErrorNum int
BEGIN TRANSACTION
    peform update 1
    SET @ErrorNum = @ERROR

    IF @ErrorNum = 0
       BEGIN
          perform update 2
         SET @ErrorNum = @ERROR
       END

IF @ErrorNum = 0
   COMMIT TRANSACTION
ELSE
   ROLLBACK  TRANSACTION

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

>>If an update statement blows up and the stored procedure abends, do all of the update statements get rolled back if you have a transaction defined?

no, not if you have a non trapable transaction which aborts the batch, you will need to set XACT_ABORT ON for it to roll back

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

Opps, I forgot TRY and CATCH in SQL Server 2005.
Check TRY...CATCH [SQL Server] in BOL for more info.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

try catch is not foolproof either

read Use XACT_ABORT to roll back non trapable error transactions

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

Sorry Denis, my suggestion for TRY and CATCH wasn't addressed to you.
It just occurs to me a several minutes later.
I am so focused on SQL 2000 now that I forgot complitely about it. I just wanted to said about it to jmeadows7.

About fool proof:
There is no such thing
No matter how hard you try there is always an user that is more stupid you can imagine

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP