Transaction in Sql server

Have you heared any thing like transaction in sql server. no.... Transaction is statement which have only one quick answer like work will do completely or it will not do. Example: In a bank database or any trasaction, like if user want to pay any item online then complete transaction is necessary. assume you paid bill online of mobile. you have completed the transaction. At the time of processing light gone or PC shut down. It means transaction completed or not. If transaction completed then no problem otherwise any thing can be happen. amount may be deducted from your account but may be not deposited in Mobile company account.

To handle this type of problems where transaction completion is necessary, we use transaction. If any problem occurs while working then automatically it will roll back and no effect will be happen. In sql server we are defining the transaction. In sql server we start with Begin Transaction and when we want to close the transaction then we write commit transaction. If error occurred the we will use the rollback keyword.

CREATE PROCEDURE TransferMoney
(
@Account1 Int,
@Account2 Int,
@myValue Numeric(18),
@myValue2 Numeric(18)
)
AS


-- STEP 1: Start the transaction

BEGIN TRANSACTION
-- STEP 2 & 3: Issue the Insert statement, checking @@ERROR after each statement

Insert Into Bank (Balance) Values (@MyValue)
WHERE AccountID = @Account1

IF @@ERROR = 0
BEGIN
Insert Into Bank (Balance) Values (@MyValue2)
WHERE AccountID = @Account2


IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
-- Rollback the transaction
ROLLBACK TRANSACTION
END
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
Return 

We are explaining that if transfer is completed then transaction completed or commit. Otherwise it is roll back.

Click here to go on website