触发器解析

来源：互联网发布：g76编程实例图文解说编辑：程序博客网时间：2024/06/15 08:51

表结构如下

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 表内容（记录整张表的信息，对比前后发现删除了哪条记录）：

0 0