在 INSERT INTO Table (etc) SELECT * FROM Table2 期间，如何让触发器在每个插入的行上触发? [英] How can I get a trigger to fire on each inserted row during an INSERT INTO Table (etc) SELECT * FROM Table2?

问题描述

我一直试图避免在这种特殊情况下使用游标，只是因为我不喜欢这种权衡，而且碰巧我正在使用的过程使触发器无论如何都看起来像是正确的操作过程.

I've been trying to avoid using a cursor in this particular case just because I dislike the tradeoffs, and it just so happens a process I'm using makes triggers look like the proper course of action anyway.

一个存储过程根据复杂的子句组合插入一条记录，使用插入触发器我向目标用户发送一封电子邮件，告诉他们访问一个站点.这很简单，而且效果很好.

A stored procedure inserts a record based off of a complicated mix of clauses, using an insert trigger I send an email to the target user telling them to visit a site. This is easy and works fine.

然而，另一个过程是每晚运行并重新分发所有未查看的记录.我这样做的方法是根据分配日期字段上的选择进行另一个插入.即:

However, another procedure is to run nightly and redistribute all unviewed records. The way I was doing this was to do another insert based on a select on a date field from when it was assigned. To wit:

INSERT INTO Table (ID, User, AssignDate, LastActionDate)
    SELECT 
        ID
        ,User
        ,GETDATE() [AssignDate]
        ,GETDATE() [LastModifiedDate]
    FROM Table2
        /*snip*/

触发器适用于单个插入，但上面的 select 语句仅适用于最后插入的行.有没有办法绕过这种行为?它毁了整件事！

The trigger works on individual inserts, but the select statement above only works on the last inserted row. Is there a way to get around this behavior? It ruins the whole thing!

编辑(触发代码):

ALTER TRIGGER dbo.Notify
    ON  dbo.Table
    AFTER INSERT
AS 
BEGIN

    DECLARE @EmailSender varchar(50)='Sender Profile'
    DECLARE @Identity int
    DECLARE @User varchar(20)
    DECLARE @Subject varchar(50)

    SET @Identity=@@Identity

    SELECT @User=User, @Subject='(' + CONVERT(varchar,@Identity) + ')!'
    FROM Table
    WHERE
        idNum=@Identity

    exec msdb.dbo.sp_send_dbmail
        @profile_name=@EmailSender,
        @recipients=@User
        @subject=@Subject,
        @body='//etc'

END

推荐答案

insert 触发器在批量插入时被调用一次，但是在触发器上你可以使用特殊的 inserted 表来获取所有插入的行.

The insert trigger is called once for bulk inserts, but on the trigger you can use the special inserted table to get all the inserted rows.

所以，假设您有一个像这样的插入触发器，它记录插入到 table

So, imagine you have an insert trigger like this one, that logs all the rows inserted into table

create trigger trgInsertTable 
on dbo.table
for insert
as
   insert tableLog(name)
    select name from inserted

使用此触发器，当您对 table 进行批量插入时，tableLog 将填充与插入到 table

With this trigger, when you make a bulk insert on table, the tableLog is filled with the same number of rows that were inserted to table

对于你特定的触发器，由于你需要为每一行调用一个存储过程，你需要使用一个游标:

For you specific trigger, since you need to call a stored procedure for each row, you need to use a cursor:

ALTER TRIGGER dbo.Notify
    ON  dbo.Table
    AFTER INSERT
AS 
BEGIN

    DECLARE @EmailSender varchar(50)='Sender Profile'
    DECLARE @User varchar(20)
    DECLARE @Subject varchar(50)

    DECLARE cursor CURSOR FOR
      SELECT User, '(' + CONVERT(varchar, Id) + ')!'
        FROM inserted

    OPEN cursor
    FETCH NEXT FROM cursor INTO @User, @Subject
    WHILE @@FETCH_STATUS = 0
    BEGIN
      exec msdb.dbo.sp_send_dbmail
          @profile_name=@EmailSender,
          @recipients=@User
          @subject=@Subject,
          @body='//etc'
      FETCH NEXT FROM cursor INTO @User, @Subject
    END
    CLOSE cursor
    DEALLOCATE cursor

END

我没有测试，但应该可以

I didn't tested, but should work

这篇关于在 INSERT INTO Table (etc) SELECT * FROM Table2 期间，如何让触发器在每个插入的行上触发?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！