SQL Server 触发器在多行插入时的应用方法

Question

SQL Server 触发器在多行插入时的应用方法

13

我正在维护某个代码，其中有一个触发器用于增加表中的一列。该列随后由第三方应用程序 A 使用。假设表名为test，有两列num1和num2。触发器在每次将num1插入到test中时运行。以下是触发器:

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

- Ali

2

在触发器内使用游标是一个非常糟糕的想法；游标以其缓慢和占用大量内存而闻名，而触发器中的任何内容都应该尽可能地精简和快速。基于我个人的痛苦经验，我强烈建议采用不同的方法。 - marc_s

“如果@NEWNUM2为空，无法使用COALESCE(MAX(num2),0)替换检查吗？” - JustAMartin

4个回答

3

请查看您的触发器中的inserted伪表，因为在这些操作期间它将包含多行。无论如何，您都应该始终处理触发器中的多行。

有关更多信息，请参见此处：

如何测试SQL Server触发器中的多行操作？

- Keith Adler

2

需要重写触发器以处理多行插入。不要使用变量编写触发器。所有触发器必须始终考虑到有一天会有人执行多行插入/更新/删除。

在触发器中也不应该这样递增列，如果需要递增的列号，为什么不使用标识列呢？

- HLGEM

我也不喜欢列的增量方式，甚至已经在Oracle中更改了触发器以使用序列。至于身份标识，我的第一个想法是使用alter table将其更改为身份标识，但不幸的是这是不可能的，其他方法需要更多的更改，而我不敢尝试。 - Ali

0

正如已经指出的那样，游标可能存在问题，最好使用触发表和插入/删除表之间的连接。

以下是如何实现的示例：

ALTER TRIGGER [dbo].[TR_assign_uuid_to_some_varchar_column]
ON [dbo].[myTable]
AFTER INSERT, UPDATE
AS
BEGIN
/********************************************
 APPROACH
  * we only care about update and insert in this case
  * for every row in the "inserted" table, assign a new uuid for blanks
*********************************************/

       update t 
              set uuid_as_varchar = lower(newid()) 
       from myTable t 

        -- inserted table is populated for row updates and new row inserts
       inner join inserted i on i.myPrimaryKey = t.myPrimarykey

        -- deleted table is populated for row updates and row deletes
       left join deleted d on d.myPrimaryKey = i.myPrimaryKey

        -- only update the triggered table for rows applicable to the trigger and
        -- the condition of currently having a blank or null stored for the id
       where 
           coalesce(i.uuid_as_varchar,'') = '' 

           -- you can also check the row being replaced as use that as part of the conditions, e.g. 
           or ( coalesce(i.uuid_as_varchar,'') <> coalesce(d.uuid_as_varchar,'') );
       
END

- beaudet

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- particle · Accepted Answer

你只需要在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;