SQL触发器

--创建交易详细表
 use MoneyManager
 if(exists(select * from sysobjects where name='transinfo'))
    drop table transinfo
 create table transinfo
 (
    id varchar(20) primary key not null,
    transdate date,
    tarnstype nvarchar(2),
    transmoney money,
    remark nvarchar(256)
 )
 go
 --给表transinfo创建插入触发器，给交易表插入一条交易信息，更新账户余额。
 alter trigger tg_transifno_insert
 on transinfo
 after insert --for等价于after是已经插入基表，可以回滚回去。instead of 还没有插入基表

 as
 declare @userid varchar(20)
 declare @money money
 declare @type nvarchar(2)
 declare @yue money
 set nocount on
 select @userid=id,@money=transmoney,@type=tarnstype from inserted
 if(@type='支取')
 begin 
       
       select  @yue=[money] from t_money where userid=@userid
       print('支取前的余额是:'+convert(varchar(10),@yue))
       update t_money set [money]=[money]-@money where userid=@userid
       print('支取了：'+convert(varchar(10),@money))
       select  @yue=[money] from t_money where userid=@userid
       print('支取后的余额是:'+convert(varchar(10),@yue))
 end
 else if(@type='存入')
 begin
       
        select  @yue=[money] from t_money where userid=@userid
        print('存入前的余额是:'+convert(varchar(10),@yue))
        update t_money set [money]=[money]+@money where userid=@userid
        print('存入了:'+convert(varchar(10),@money))
        select  @yue=[money] from t_money where userid=@userid
        print('存入后的余额是:'+convert(varchar(10),@yue))
 end
 go
 --测试语句
 insert into transinfo values (2222,GETDATE(),'支取',300,'');
 go
 --给表transinfo创建删除触发器，删除一条交易信息，并备份到transbacktable表
 alter trigger tg_transinfo_delete
 on transinfo
 for delete
 as
    set nocount no
    if(not Exists(select * from sysobjects where name='transbacktable'))
    begin
     select * into transbacktable from deleted
     end
     else
     begin
        insert into transbacktable select * from deleted
     end
 
 go
 --测试语句
    delete from dbo.transinfo where id='2222'
 go
  --给表t_money创建更新发器，单笔交易大于500就停止交易
  create trigger tg_t_money_update
  on t_money
  for update
  as
    set nocount no
    declare @beformoney money
    declare @aftermoney money
    select @beformoney=[money] from deleted
    select @aftermoney=[money] from inserted
    if(abs(@aftermoney-@beformoney)>500)
    begin
        raiserror('单笔交易不能大于500',16,1)
        rollback transaction
    end
 go
 --测试语句
 update t_money set [money]=[money]+500
 go
  --给表transinfo的transdate列创建更新发器，禁止更新
  create trigger tg_transinfo_transdateupdate
  on transinfo
  for update
  as
  set nocount on
  if(update(transdate))
  begin
      raiserror('禁止更改交易时间',16,1)
      rollback transaction
  end
  go