触发器解析

表结构如下

SELECT TOP 1000 [id]
      ,[cardID]
      ,[currentMoney]
  FROM [pubs].[dbo].[bank]

SELECT TOP 1000 [id]
      ,[transType]
      ,[transMoney]
      ,[cardID]
  FROM [pubs].[dbo].[bank_czmoney]

一、创建插入

CREATE trigger [dbo].[trig_bank_insert] on [dbo].[bank_czmoney]
    for insert
    as
    declare @type nvarchar(50),@xmoney money,@cardid nvarchar(50)     
    begin
        select @cardid=cardID,@type=transType,@xmoney=transMoney from inserted --取插入的数据
        IF(@type = '收入')
            update bank set currentMoney=(currentMoney + @xmoney) where cardID=@cardid  
        else
            update bank set currentMoney=(currentMoney - @xmoney) where cardID=@cardid  
            end            
GO

二、创建删除

CREATE TRIGGER trig_bank_delete
      ON bank_czmoney FOR DELETE  
      AS  
     declare @id int
     declare @transType nvarchar(50)
     declare @transMoney money
     declare @cardID nvarchar(50)
     select @id = id, @transType = transType,@transMoney=transMoney,@cardID=cardID from deleted --取删除的数据
          IF not exists (SELECT * FROM sysobjects where name = 'backupTable')
          select * into backupTable from bank_czmoney
          DELETE FROM backupTable
          insert into backupTable (id,transType,transMoney,cardID) VALUES (@id,@transType,@transMoney,@cardID)  --插入另一个表
            
三、创建修改

CREATE trigger trig_bank_update on bank_czmoney
    for update
    as
    declare @type nvarchar(50),@xmoney money,@cardid nvarchar(50)     
    begin         
        select @cardid=cardID,@type=transType,@xmoney=transMoney from bank_czmoney
        IF(@type = '收入')
            update bank set currentMoney=(currentMoney + @xmoney) where cardID=@cardid  
        else
            update bank set currentMoney=(currentMoney - @xmoney) where cardID=@cardid  
            end
            
backupTable 表内容（记录整张表的信息，对比前后发现删除了哪条记录）：