sql-server – SQL Server用于处理多行插入的触发器

发布时间：2020-12-12 06:41:34 所属栏目：MsSql教程来源：网络整理

导读：我正在维护一些代码,这些代码在表上有一个触发器来增加列.该列然后由第三方应用程序A使用.假设该表名为test,其中包含两列num1和num2.触发器在测试中的num1的每个插入上运行.以下是触发器： USE [db1]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TR

我正在维护一些代码,这些代码在表上有一个触发器来增加列.该列然后由第三方应用程序A使用.假设该表名为test,其中包含两列num1和num2.触发器在测试中的num1的每个插入上运行.以下是触发器：

USE [db1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TEST_MYTRIG] ON [dbo].[test]
FOR INSERT AS
begin
SET NOCOUNT ON
DECLARE @PROC_NEWNUM1 VARCHAR (10)
DECLARE @NEWNUM2 numeric(20)

SELECT @PROC_NEWNUM1 = num1 FROM INSERTED
select @NEWNUM2 = MAX(num2) from TEST
if @NEWNUM2 is null
Begin
set  @NEWNUM2  = 0
end
set @NEWNUM2 = @NEWNUM2 + 1
UPDATE TEST SET num2 = @NEWNUM2 WHERE num1 = @PROC_NEWNUM1
SET NOCOUNT OFF
End

这在基于行的简单插入中工作得很好,但还有另一个第三方应用程序B(叹气),有时在这个表上有多个这样的插入,但不完全相同：

INSERT INTO [db1].[dbo].[test]
           ([num1])

   Select db1.dbo.test.num1 from [db1].[dbo].[test]
GO

这会导致触发器表现不正常……

现在我无法访问应用程序A或B的源代码,只能控制数据库和触发器.是否可以使用触发器执行任何操作,以便在多次插入的情况下对num2执行的更新是正确的？

解：

以下是基于affan代码的解决方案：

DECLARE @PROC_NEWNUM1 VARCHAR (10)
 DECLARE @NEWNUM2 numeric(20)
 DECLARE my_Cursor CURSOR FAST_FORWARD FOR SELECT num1 FROM INSERTED;

 OPEN my_Cursor 
 FETCH NEXT FROM my_Cursor into @PROC_NEWNUM1

 WHILE @@FETCH_STATUS = 0 
 BEGIN 

 select @NEWNUM2 = MAX(num2) from TEST
 if @NEWNUM2 is null
 Begin
    set  @NEWNUM2  = 0
 End
 set @NEWNUM2 = @NEWNUM2 + 1
 UPDATE TEST SET num2 = @NEWNUM2  WHERE num1 = @PROC_NEWNUM1
 FETCH NEXT FROM my_Cursor into @PROC_NEWNUM1  
 END

CLOSE my_Cursor
DEALLOCATE my_Cursor

点击此处查看基于集合的方法：
SQL Server – Rewrite trigger to avoid cursor based approach

解决方法

你只需要在INSERTED上打开一个游标并为@ PROC_NEWNUM1迭代它并将你剩下的代码循环.例如

DECLARE @PROC_NEWNUM1 VARCHAR (10)
 DECLARE @NEWNUM2 numeric(20)
 DECLARE my_Cursor CURSOR FOR SELECT num1 FROM INSERTED; 
 OPEN my_Cursor; 

 FETCH NEXT FROM @PROC_NEWNUM1; 


 WHILE @@FETCH_STATUS = 0 
 BEGIN FETCH NEXT FROM my_Cursor 
 select @NEWNUM2 = MAX(num2) from TEST
 if @NEWNUM2 is null
 Begin
  set  @NEWNUM2  = 0
 end
 set @NEWNUM2 = @NEWNUM2 + 1
 UPDATE TEST SET num2 = @NEWNUM2 WHERE num1 = @PROC_NEWNUM1

 END; 

CLOSE my_Cursor; DEALLOCATE my_Cursor;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!