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