Managing Data Manipulation Using Transactions


  • Manage data manipulation by using transactions.

    • Implement error handling in transactions.

Now that you know how to raise error messages to the user and the operating system, it's time to learn how to handle the error internally, so your data integrity is preserved. Basically, you use a combination of the material you've already examined in this chapter: the global variable @@TRANCOUNT , the ROLLBACK TRANSACTION statement, and RAISERROR .

Consider the scenario described earlier to explain why transactions are important: the bank transaction. This time, there's an Account Balance table, which contains the account number and the balance.

Here's the same transaction to move $100 from account 64 into account 42. (This block also builds the tables for you, to help you follow along.)

 CREATE TABLE AccountBalance (         AccountID int,         Balance float ) go INSERT INTO AccountBalance VALUES (64, 500.00) INSERT INTO AccountBalance VALUES (42, 500.00) go BEGIN TRANSACTION UPDATE AccountBalance SET balance = balance + 100 WHERE AccountID = 42 UPDATE AccountBalance SET balance = balance - 100 WHERE AccountID = 64 COMMIT TRANSACTION 

Now, this looks fine, but how can we check the account balance to make sure we haven't overdrawn account 64? One approach would be

[View full width]
 
[View full width]
BEGIN TRANSACTION UPDATE AccountBalance SET balance = balance + 100 WHERE AccountID = 42 UPDATE AccountBalance SET balance = balance - 100 WHERE AccountID = 64 IF (SELECT Balance FROM AccountBalance WHERE AccountID = 64) < 0 BEGIN RAISERROR ('Account Overdrawn for %s, transaction cancelled.', 10, 1, graphics/ccc.gif '64') ROLLBACK TRANSACTION END COMMIT TRANSACTION

The problem with this code is that eventually it causes an error because there's an attempt to commit the transaction after it's been rolled back. When the account balance in AccountID 64 falls to zero, the error is raised, and then the transaction is rolled back. The problem is that the script continues on and attempts to commit, but there is no transaction in progress, because it's been rolled back. There are two ways to fix this problem. You could simply put the COMMIT TRANSACTION into an ELSE clause, but what if there were more tests than this one? It would be nice to know whether a transaction is pending before the attempt to commit the transaction is made ”something like this:

 BEGIN TRANSACTION UPDATE AccountBalance SET balance = balance + 100 WHERE AccountID = 42 if @@ERROR <> 0         BEGIN                RAISERROR('Unable to credit account', 16, 1)                ROLLBACK TRANSACTION                RETURN         END UPDATE AccountBalance SET balance = balance - 100 WHERE AccountID = 64 if @@ERROR <> 0         BEGIN                RAISERROR('Unable to debit account', 16, 1)                ROLLBACK TRANSACTION                RETURN         END IF (SELECT Balance FROM AccountBalance WHERE AccountID = 64) < 0 BEGIN         RAISERROR ('Account Overdrawn for %s, transaction cancelled.', 10, 1, '64')         ROLLBACK TRANSACTION END IF @@TRANCOUNT > 0 COMMIT TRANSACTION 

That way, if more complex operations needed to take place inside the transaction, there's an easy way to check to see whether a commit needs to happen when it's all over.

So, now you can write transactions that move millions of dollars around inside your database safely. How do you make sure that the other people who are using your database are moving money around properly?



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net