How can I get a trigger to fire on each inserted row during an INSERT INTO Table (etc) SELECT * FROM Table2?

Question

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*/

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!

Edit (trigger code):

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

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.

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

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

Answer 2

If you are sending an email, I would not do that from a trigger. Do you really want people to not be able to insert records because the email server is down?

It is usually better to insert the records to a table from the trigger and then have a job that sends the emails that runs every minute or so and updates the email status to sent and adds the sent datetime to the table when each record is sent. This not only allows you to insert records when emails are down, it moves the looping to send each individual email to a table the users are not accessing (and thus any delay processing many records will only affect the new users not anyone else) and it allows you to see a history of when you sent the email which helps when people question why they didn't get it. You can also record in the table if the email failed to send to help identify bad email addresses.