我正在维护一些代码,这些代码在表上有一个触发器来增加列.该列然后由第三方应用程序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执行的更新是正确的？
解：
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;
