事务的使用示例SQL

[sql]
--事务的使用示例  
create database MyDB  
go  
use MyDB  
  
create table account  
(  
    Id int identity primary key,  
    balance int --余额  
)  
  
insert into account values(1000)  
insert into account values(200)  
  
go  
--在存储过程中使用事务，简单转帐  
create procedure transfer(@fromId int, @toId int, @total int) as  
begin  
    declare @ts varchar(30), @tb int  
    begin transaction  
    update account set balance=balance+@total where id=@toId  
    update account set @tb=balance, balance=balance-@total where id=@fromId  
    if (select balance from account where id=@fromId)>





=0  
        commit transaction  
    else  
    begin         
        set @ts=convert(varchar(30), @total) --int类型转换为字符串,(30)可省略  
        raiserror('余额%d不足以转帐%s', 16, 1, @tb, @ts)--%d,%s为格式控制符  
        rollback transaction  
    end  
end  
  
go  
  
exec transfer 1, 2, 1234 --测试，转帐不成功  
select * from account  
exec transfer 1, 2, 500 --测试，转帐成功  
select * from account