Reprinted with Permission by Quest Software Nov. 2001

Error Checking with Transactions

Transactions follow the ALL or NOTHING rule ; therefore, you need to make sure no errors are encountered during transaction execution. Error checking is accomplished by examining the value of the global variable @@error. If this variable has a value of anything other than zero, that means trouble. So, making sure that your transactions are atomic is fairly simple. Consider the following example scenario, we have the sales history table in the pubs database that tracks total sales for each title during the given year :

CREATE TABLE sales_history (title_id VARCHAR(15) NOT NULL, yr_sold INT NOT NULL, total_sales INT NULL)

INSERT sales_history (title_id, yr_sold)
SELECT distinct title_id, 2001 FROM titles

The following batch inserts a new record to the sales table and updates the sales history table appropriately. Notice the usage of the @@error global variable to add error checking:

BEGIN TRAN
INSERT sales
VALUES ('6380', '34ca', '1/2/01', 4, 'net 40', 'bu1032')

IF @@ERROR <> 0
BEGIN
	RAISERROR ('an error encountered when inserting new sales record', 16, 1) WITH LOG
	ROLLBACK TRAN
	RETURN 
END

UPDATE sales_history SET total_sales = CASE
WHEN total_sales IS NULL THEN 4 ELSE total_sales + 4 END
WHERE title_id = 'bu1032'

IF @@ERROR <> 0
BEGIN
	RAISERROR ('an error encountered when updating the sales history', 16, 1) WITH LOG
	ROLLBACK TRAN
	RETURN
END

COMMIT TRAN

Now, if we wanted to go on and save our insert even if the subsequent update of the history table fails we could do so by using savepoints, as follows:

BEGIN TRAN new_sales_record
INSERT sales
VALUES ('6380', '34ca', '1/2/01', 4, 'net 40', 'bu1032')

IF @@ERROR <> 0
BEGIN
	RAISERROR ('an error encountered when inserting new sales record', 16, 1) WITH LOG
	ROLLBACK TRAN new_sales_record
	RETURN 
END

SAVE TRAN insert_sale_1

UPDATE sales_history SET total_sales = CASE
WHEN total_sales IS NULL THEN 4 ELSE total_sales + 4 END
WHERE title_id = 'bu1032'

IF @@ERROR <> 0
BEGIN
	RAISERROR ('an error encountered when updating the sales history', 16, 1) WITH LOG
	ROLLBACK TRAN insert_sale_1
	RETURN
END

COMMIT TRAN new_sales_record

Notice that the first ROLLBACK statement rolls back the entire transaction, since we don't want to save anything if the initial insert fails. However, the second ROLLBACK only rolls back part of the transaction up to the save point. We've added a transaction name to this second example to make it more readable and self-documenting.