Transactions

Transactions:
Set of database commands that are treated as a single unit.
Commands can be treated as transaction if
Atomic :Each command should perform single unit of work independently.
Consistent:All the relations between data are maintained correctly
Isolated:Changes made by other changes should not effect the current changes
Durable:Once a change is made , it should be permanent.

Transaction follow the steps given below:
1.Execute the commands
2.Checks whether there is any error , if there is any error , then restore the data base to its state at the beginning of transaction.ie, Roll back the transaction.
3.Else commit the Transaction
Here I have taken an example of an virtual bank transfer.when we provide the account number of the sender and receiver and the amount to be transfered. we need to deduct that amount from the sender and then add the same amount to the receiver's Account.Given Below is an Stored procedure that can be used for this

Code Snippet


CREATE PROCEDURE [dbo].[SP_UPDATETRANSACTION]
@ACC_FROM VARCHAR(20),@ACC_TO VARCHAR(20),@AMT VARCHAR(20)
AS
DECLARE @BALANCEFROM INT ,@BALANCETO INT ,@DIFF INT

SET @BALANCEFROM =(SELECT BALANCE FROM TransactionDetails WHERE ACCOUNT_NAME =@ACC_FROM)
SET @BALANCETO =(SELECT BALANCE FROM TransactionDetails WHERE ACCOUNT_NAME =@ACC_TO)
SET @DIFF =@BALANCEFROM -@AMT 

BEGIN TRAN
UPDATE TransactionDetails  set Balance=@BALANCEFROM-@AMT  where ACCOUNT_NAME =@ACC_FROM 
UPDATE TransactionDetails  set Balance=@BALANCETO+@AMT  where ACCOUNT_NAME =@ACC_TO 

IF(@DIFF <0)
BEGIN
ROLLBACK TRAN
 PRINT 'No sufficient fund for Transfer'
END
 ELSE
 COMMIT TRAN

HappyCoding ! đŸ˜‰

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: