触发器批量业务操作实现方式 update触发器用游标处理
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_InsertAndUpdate]
ON [dbo].[Shop_OrderAmount]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @oldQty int,
@newQty int,
@ShopGid uniqueidentifier,
@ProdGid uniqueidentifier,
@tempShop Nvarchar(MAX)
--更新前的数据
select @oldQty=Qty,
@ShopGid =ShopGid,
@ProdGid=ProdGid
from deleted;
BEGIN
--更新后的数据
select @newQty = Qty from inserted;
--声明游标
declare cursorShopQty cursor for
(select DISTINCT A.Gid from inserted A
inner join deleted B on A.Gid=B.Gid
)
--打开游标--
open cursorShopQty
--开始循环游标变量--
fetch next from cursorShopQty into @tempShop
--返回被 FETCH语句执行的最后游标的状态--
while @@FETCH_STATUS = 0
if (update(Qty) and @newQty<>@oldQty)
BEGIN
INSERT INTO Shop_OrderLog
(Gid,ShopGid,ProdGid,Qty,UserQty, CreateDate)
select NEWID(),
B.Gid,B.ShopGid,B.ProdGid,B.Qty, isnull(A.Qty,0)-ABS(B.Qty),
GETDATE()
from inserted A inner join deleted B on A.Gid=B.Gid where A.Gid=@tempShop
--转到下一个游标,没有会死循环
fetch next from cursorShopQty into @tempShop
END
close cursorShopQty --关闭游标
deallocate cursorShopQty --释放游标
END
END
游标处理:触发器中DELETE和INSERTED其实是两张虚表,因此用变量获取仅能得到一条数据,但是如果用游标的话可以获取到位于虚表中的所有数据,批量处理
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_InsertAndUpdate]
ON [dbo].[Shop_OrderAmount]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @oldQty int,
@newQty int,
@ShopGid uniqueidentifier,
@ProdGid uniqueidentifier
--更新前的数据
select @oldQty=Qty,
@ShopGid =ShopGid,
@ProdGid=ProdGid
from deleted;
BEGIN
--更新后的数据
select @newQty = Qty from inserted;
if (update(Qty) and @newQty<>@oldQty)
BEGIN
INSERT INTO Shop_OrderLog
(Gid,ShopGid,ProdGid,Qty,UserQty, CreateDate)
select NEWID(),
B.Gid,B.ShopGid,B.ProdGid,B.Qty, isnull(A.Qty,0)-ABS(B.Qty),
GETDATE()
from inserted A inner join deleted B on A.Gid=B.Gid
END
END
未使用游标处理:触发器中DELETE和INSERTED是两张虚表,那么其实上述的操作也可以这么来