批量插入的触发触发器 [英] Firing trigger for bulk insert

问题描述

ALTER TRIGGER [dbo].[TR_O_SALESMAN_INS]
   ON  [dbo].[O_SALESMAN]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    DECLARE @SLSMAN_CD NVARCHAR(20)
    DECLARE @SLSMAN_NAME NVARCHAR(20)
    SELECT @SLSMAN_CD = SLSMAN_CD,@SLSMAN_NAME=SLSMAN_NAME
    FROM INSERTED

    IF NOT EXISTS(SELECT * FROM O_SALESMAN_USER WHERE SLSMAN_CD = @SLSMAN_CD)
    BEGIN
        INSERT INTO O_SALESMAN_USER(SLSMAN_CD, PASSWORD, USER_CD)
        VALUES(@SLSMAN_CD, @SLSMAN_CD,@SLSMAN_NAME )
    END
END

这是为 table(O_SALESMAN) 编写的触发器，用于从中获取几列并将其插入另一个 table(O_SALESMAN_USER).目前，批量数据通过存储过程插入到 O_SALESMAN 表中，其中触发器仅被触发一次，并且 O_SALESMAN_USER 每次只插入一条记录正在执行过程，我希望触发器在插入到 O_SALESMAN 中的每条记录之后运行，这样两个表都应该具有相同的计数，这不会发生..所以请让我知道可以修改的内容在这个触发器中实现相同....

This is the trigger written for a table(O_SALESMAN) to fetch few columns from it and insert it into one another table(O_SALESMAN_USER). Presently bulk data is getting inserted into O_SALESMAN table through a stored procedure, where as the trigger is getting fired only once and O_SALESMAN_USER is having only one record inserted each time whenever the stored procedure is being executed,i want trigger to run after each and every record that gets inserted into O_SALESMAN such that both tables should have same count which is not happening..so please let me know what can be modified in this Trigger to achieve the same....

推荐答案

您可以按如下方式使用游标:

you can use a cursor as follows:

create trigger trg_insertstuff
on [O_SALESMAN]
after insert
as

  DECLARE @SLSMAN_CD NVARCHAR(20)
    DECLARE @SLSMAN_NAME NVARCHAR(20)


 declare db_cursor CURSOR FOR  
SELECT SLSMAN_CD, SLSMAN_NAME
from inserted

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @SLSMAN_CD , @SLSMAN_NAME 

WHILE @@FETCH_STATUS = 0  
BEGIN  

IF NOT EXISTS(SELECT * FROM O_SALESMAN_USER WHERE SLSMAN_CD = @SLSMAN_CD)
    BEGIN
        INSERT INTO O_SALESMAN_USER(SLSMAN_CD, PASSWORD, USER_CD)
        VALUES(@SLSMAN_CD, @SLSMAN_CD,@SLSMAN_NAME )
    END


FETCH NEXT FROM db_cursor INTO  @SLSMAN_CD , @SLSMAN_NAME 
end

CLOSE db_cursor  
DEALLOCATE db_cursor

这篇关于批量插入的触发触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！