如果在一条插入语句中插入多条记录，如何使用inserttrigger

Question

我想在一个插入语句中插入多个记录，此时我在触发器中得到错误

错误

Msg 512, Level 16, State 1, Procedure SetIntFlag, Line 7 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

my trigger is

declare @act char(16)if update (first_nm)
set @act = (select acct_no from inserted)
update Client set Phn9_int = 1 where acct_no = @act

Answer 1

假设这是整个触发器:

update Client set Phn9_int = 1 where acct_no in (
    select acct_no from inserted)

基本上，我的经验法则是，如果您在触发器中看到任何标量变量，那么它可能与多行操作有关。如果使用游标则例外，但游标可以通常避免。

Answer 2

声明光标已插入并使用，代码应该如下所示:

DECLARE @act char(16)
DECLARE my_Cursor CURSOR FAST_FORWARD FOR SELECT acct_no FROM INSERTED;
OPEN my_Cursor 
FETCH NEXT FROM my_Cursor into @act
WHILE @@FETCH_STATUS = 0 
BEGIN 
     UPDATE Client SET Phn9_int = 1 where acct_no = @act
     FETCH NEXT FROM my_Cursor into @act 
END
CLOSE my_Cursor
DEALLOCATE my_Cursor

Answer 3

不要在WHERE子句中使用local_variable作为条件。而是使用简单的JOIN条件

if update (first_nm)
update c
set c.Phn9_int = 1
FROM Client c JOIN inserted i ON c.acct_no = i.acct_no