Quick Start Guide to SQL Server 7 -- Part 4

Transactions allow you to complete numerous database operations as one whole unit.

Let's take the common example of bank transactions. Mr Bloggs walks into his local Natwest and hands over a cheque given to him by Mr Smith.

After this is tapped into the computer, your application may remove the money from Mr Smith's account... and then crash. That means Mr Smith has lost his money and Mr Bloggs is left wondering what's happened to his cheque payment.

Or perhaps your system first adds the money to Mr Smith's account, then attempts to deduct it from the account of Mr Bloggs. What happens if Mr Bloggs doesn't have adequate cash reserves? That means Mr Smith now has money in his account that shouldn't really be there.

All these problems are solvable with transactions. You can 'start a transaction', then take the money from Mr Smith, give it to Mr Bloggs, then 'commit the transaction'.

Committing the transaction tells SQL Server everything is fine.

However you can also 'rollback the transaction'. After deducting money from Mr Smith and adding it to the bank account of Mr Bloggs, you might experience an error. Maybe Mr Bloggs has since closed down his account, or something similar.

You can then 'rollback' (cancel) the transaction, meaning Mr Smith doesn't have any money deducted, nor Mr Bloggs have any money added.

In other words, both items of work are either completed or rejected as one entire unit not individual database operations.

Let's now look at a simple Visual Basic transaction. This code attempts to add two extra records to the Jobs table. It then prompts the user if they want to commit or rollback.

If they commit, both records should be added. If they abort, neither should be added.

In real life however, these records would probably be in different tables. And transactions aren't limited to record adding. You can commit and rollback anything from stored procedures to table creation.

And don't forget that you wouldn't usually prompt a user to commit or rollback. You would typically commit or rollback in code depending on whether you received errors (eg, as a result of data integrity, etc).

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objRS As New ADODB.Recordset' Objects to be used in this operation -' objConn, the basic connection' objRS, to hold our recordsetWith objConn.ConnectionString = "Driver=SQL Server;Server=COLOSSI;" & _"Database=PUBS;User ID=KarlMoore;Password=TEST".OpenEnd With' Get a connection to the databaseobjConn.BeginTrans' Begin Transactions here!objRS.Open "Select * from Jobs", objConn' Open the recordset, selecting all from the Jobs tableobjRS.AddNewobjRS.Fields("job_desc") = "My New Test Job"objRS.Fields("min_lvl") = 10objRS.Fields("max_lvl") = 20objRS.Update' Add a new record... and then...objRS.AddNewobjRS.Fields("job_desc") = "My Second New Test Job"objRS.Fields("min_lvl") = 20objRS.Fields("max_lvl") = 30objRS.Update' ... another!If MsgBox("Do you want to commit?", vbYesNo + vbQuestion) = vbYes ThenobjConn.CommitTransElseobjConn.RollbackTransEnd If' Run the CommitTrans or RollbackTrans methods,' depending on user responseobjRS.CloseSet objRS = NothingobjConn.CloseSet objConn = Nothing' Close all references End Sub