SQL function: Transaction instance

A transaction is responsible for taking a series of operations as an independent logical unit. These operations either succeed or fail at the same time. The following is a classic example:

Create
 
Procedure
TransferMoeny
(

@ FromAccountNo
 
Varchar
(
50
),
--
Transfer Account



@ ToAccountNo
 
Varchar
(
50
),
--
Transfer Account



@ MoneyCount
 
Money
--
Transfer amount


)

As


--
Determine whether the account exists


If
 
Exists
(
Select
 
1
 
From
Account table
Where
Account
=
 
@ Fromaccountno
)

Begin


If
 
Exists
(
Select
 
1
 
From
Account table
Where
Account
=
 
@ Toaccountno
)

Begin


--
Determine whether the transfer amount is greater than the current balance



If
(
Select
Current Balance
From
Account table
Where
Account
=
 
@ FromAccountNo
)
> =
 
@ MoneyCount


Begin


--
Start Transfer



Begin
 
Transaction


Insert
 
Into
 
[
Access record table
]
(
[
Account
]
,
[
Access type
]
,
[
Access amount
]
)
Values
(
@ Fromaccountno
,
-
1
,
@ Moneycount
)

If
 
@ Error
 
<>
 
0


Begin

Rollback
 
Transaction
--
If an error occurs, roll back the transaction and exit unconditionally.



Return


End


Insert
 
Into
 
[
Access record table
]
(
[
Account
]
,
[
Access type
]
,
[
Access amount
]
)
Values
(
@ Toaccountno
,
1
,
@ Moneycount
)

If
 
@ Error
 
<>
 
0


Begin

Rollback
 
Tran

Return


End


Commit
 
Transaction
 
--
Both statements are completed and the transaction is committed.



End


Else


Raiserror
(
'
The transfer amount cannot exceed the account balance
'
,
16
,
1
)

End


Else


Raiserror
(
'
The transfer account does not exist.
'
,
16
,
1
)

End


Else


Raiserror
(
'
The outbound account does not exist.
'
,
16
,
1
)