SQL触发器使用游标获取批量数据

93 阅读 0 评论 62 点赞

我是靠谱客的博主跳跃狗，这篇文章主要介绍SQL触发器使用游标获取批量数据，现在分享给大家，希望可以做个参考。

有点概念需要解释一下：

1、每一次insert，update，delete都会触发触发器；

2、每一次的insert，update，delete可能有n条数据。

例如SQL语句update table-name set XXX=XXX where Id>=30

这种情况我们熟称批量处理，此时inserted和deleted里面确实会有30条数据（假设Id从1开始自增加），但触发器只会触发一次。

所以单单用触发器，你只会取到一条数据。这个时候可以借助游标cursor获取所有数据。

带有游标的SQL触发器程序如下：（我的应用是当A表有insert，update和delete时，把相应的数据转存到另外的B表）

CREATE TRIGGER [EffdeleteTrigger]
ON [dbo].[ProductionEfficiency]
FOR INSERT,UPDATE,DELETE
AS
DECLARE @strKeyGroup varchar(200),
@iEfficiencyID int,
@strShiftCode varchar(10),
@iIdle decimal(5,2); --这三个是需要保存取出来的字段变量

begin
   IF EXISTS( SELECT 1 FROM inserted) --插入和更新
   declare cur cursor forward_only for select EfficiencyID,ShiftCode,Idle from inserted
   ELSE --删除
   declare cur cursor forward_only for select EfficiencyID,ShiftCode,Idle from deleted

   open cur --打开游标，注意黄色部分个数、类型一定要对应，否则会出错
   fetch next from cur into @iEfficiencyID,@strShiftCode@iIdle while(@@FETCH_STATUS=0) --此句已经把字段保存到了变量中
   begin
     select @strKeyGroup=@strShiftCode+','+cast(@iIdle as varchar(10));

     IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted) --插入
  INSERT INTO DeleteTables (TableName,TableKeyGroup,OperatorType,TableID) VALUES('ProductionEfficiency',@strKeyGroup,'insert',@iEfficiencyID) --DeleteTables是我上面提到的B表

  IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)   --更新
  INSERT INTO DeleteTables (TableName,TableKeyGroup,OperatorType,TableID) VALUES('ProductionEfficiency',@strKeyGroup,'update',@iEfficiencyID)

  IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) --删除
  INSERT INTO DeleteTables (TableName,TableKeyGroup,OperatorType,TableID) VALUES('ProductionEfficiency',@strKeyGroup,'delete',@iEfficiencyID)

  fetch next from cur into @iEfficiencyID,@strShiftCode,@iIdle   --指向下一条
   end
   close cur --关闭游标
   deallocate cur --销毁游标资源
end