Transaction Example

//First Example

USE pubs


DECLARE @intErrorCode INT


BEGIN TRAN
UPDATE Authors
SET Phone = '415 354-9866'
WHERE au_id = '724-80-9391'


SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM


UPDATE Publishers
SET city = 'Calcutta', country = 'India'
WHERE pub_id = '9999'


SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN


PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END


//Second Example

Create Proc TranTest2
AS
BEGIN TRAN

INSERT INTO [authors]([au_id], 
 [au_lname], 
 [au_fname], 
 [phone], 
 [contract])
VALUES ('172-32-1176', 
 'Gates', 
 'Bill', 
 '800-BUY-MSFT', 
 1)

IF @@ERROR <> 0
   BEGIN
 ROLLBACK TRAN
 return 10
   END

UPDATE  authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172-32-1176'

IF @@ERROR <> 0
   BEGIN
 ROLLBACK TRAN
 return 11
   END

COMMIT TRAN
GO