关于事务，存储过程，触发器的简单例子

用一个简单的Bank表分别对事务，存储过程和触发器写个简单例子

Bank表有连个字段：Id（账号）varchar(4);

　　　　　　　　　　Balance(账号金额）money （check约束 Balance>10)

1.事务（转账）

begin transaction
declare @error int;
set @error=0;
update bank set Balance=Balance-1000 where Id='0001';
set @error=@error+@@ERROR;
update bank set Balance=Balance+1000 where Id='0002';
set @error=@error+@@ERROR;
if @error>0
begin
 rollback transaction
end
else
begin
 commit transaction
end

使用try-catch）(减少代码量）

begin transaction
begin try
 update bank set Balance=Balance-1000 where Id='0001';
 update bank set Balance=Balance+1000 where Id='0002';
 commit transaction
end try
begin catch
 rollback transaction
end catch

2存储过程（转账）

alter proc usp_MoveBalance
@cash money,
@inBankId char(4),
@OutBankId char(4),
@currentCash money output,  --显示余额
@flag bit output            --是否转成功
as
begin
begin transaction
 begin try
  update bank set Balance=Balance-@cash where Id=@OutBankId;
  update bank set Balance=Balance+@cash where Id=@InBankId;
  set @flag=1;
  commit transaction 
 end try
 begin catch
  set @flag=0;
  rollback transaction
 end catch 
 select @currentCash=Balance from bank where Id=@OutBankId;
end

--执行存储过程

declare @flag bit;
declare @currentCash money;
exec usp_MoveBalance 100,'0002','0001',@currentCash output,@flag output
select @flag
select @currentCash

3.触发器-（如果给任何账户打钱，都会把钱打到'0004'这个账号）（instead of)还有另一种触发器after（写for默认是after）

create trigger TriBankUpdate on bank
instead of Update
as
begin
 declare @afterUpdateCash money;
 declare @beforeUpdateCash money;
 declare @cash money;
 select @afterUpdateCash=Balance from inserted;
 select @beforeUpdateCash=Balance from deleted;
 set @cash=@afterUpdateCash-@beforeUpdateCash;
 if @cash>0
 begin
  Update bank set Balance=Balance+@cash where Id='0004';
 end
end

--------------------------------------

--测试

select * from bank

update bank set Balance=Balance+1000 where Id='0001';