触发器trigger记录前后数据变化

触发器内置了deleted和inserted内存表，通过ID关联就能够取得已经删除的老数据(deleted表)，和插入的新数据（inserted表），update是先delete后insert.

但是不推荐用触发器记录,会拖慢速度, 更新几笔则会触发几次,建议使用存储过程

alter   trigger [dbo].[Inventory_KcnumberChangeRcord]

on  [dbo].[A]

for update 

as

BEGIN 

if update(kcnumber)

begin

DECLARE @id int 

DECLARE @oldKCnumber  as float

DECLARE @KCnumber  as float

DECLARE @type as  nvarchar(50)

--SELECT * INTO #INS FROM INSERTED

DECLARE @orderID nvarchar(20)

DECLARE @sysnumber nvarchar(20)

DECLARE V_CURSOR CURSOR SCROLL  dynamic  --动态

FOR

Select  id,KCnumber   From inserted 

OPEN V_CURSOR    --打开游标

fetch next from V_CURSOR into @id,@KCnumber --开始抓取第一条数据 

WHILE(@@fetch_status=0)         --语句成功  有数据

begin 

SET @oldKCnumber=( SELECT top 1  A.Kcnumber from deleted  A where A.id=@id )

insert into  dbo.[Inventory_+](Inventory_Id,OLDKCnumber,KCnumber,type,createtime)

select @id ,@oldKCnumber   ,@KCnumber as KCnumber,'',GETDATE()

FETCH NEXT FROM V_CURSOR INTO @orderID,@sysnumber

end 

CLOSE V_CURSOR

DEALLOCATE V_CURSOR 

--insert into DB_HekeLog.dbo.[N_Inventory_+](Inventory_Id,OLDKCnumber,KCnumber,type,createtime)

--select A.id, A.KCnumber ,B.KCnumber ,'+',GETDATE()

--FROM  deleted  A,inserted   B

--WHERE A.ID=B.ID    AND 

end 

END