如何在 INSERT INTO Table（等）SELECT * FROM Table2 期间触发每个插入行的触发器？

Question

我一直试图避免在这种特殊情况下使用游标，只是因为我不喜欢这种权衡，而我正在使用的过程恰好使触发器看起来像是正确的操作过程。

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

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

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

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

编辑（触发代码）：

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

Answer 1

对于批量插入，插入触发器会被调用一次，但在触发器上，您可以使用特殊的 inserted 表来获取所有插入的行。

因此，假设您有一个像这样的插入触发器，它记录插入到 table 中的所有行。

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

使用此触发器，当您在 table 上进行批量插入时，>tableLog 填充的行数与插入到 table 中的行数相同。

对于您的特定触发器，由于您需要为每一行调用存储过程，因此需要使用游标：

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

Answer 2

如果您要发送电子邮件，我不会通过触发器来发送电子邮件。您真的希望人们因为电子邮件服务器关闭而无法插入记录吗？

通常最好将记录从触发器插入到表中，然后有一个作业发送每分钟左右运行的电子邮件，并将电子邮件状态更新为已发送，并在发送每条记录时将发送的日期时间添加到表中。这不仅允许您在电子邮件关闭时插入记录，还可以移动循环以将每封单独的电子邮件发送到用户未访问的表（因此处理许多记录的任何延迟只会影响新用户，而不影响其他任何人）允许您查看发送电子邮件的历史记录，当人们质疑为什么没有收到电子邮件时，这会有所帮助。您还可以在表格中记录电子邮件发送失败的情况，以帮助识别错误的电子邮件地址。