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

Question

I tried to avoid using the cursor in this particular case just because I don’t like compromises, and this is how the process that I use happens because the triggers look like the right course of action.

A stored procedure inserts a record based on a complex mix of sentences using an insert trigger. I am sending an email to the target user who tells them to visit the site. It works easily and perfectly.

However, another procedure is to run every night and distribute all unsolicited entries. The way I did this was to make another insert based on the choice in the date field 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 with individual inserts, but the select statement above only works with the last inserted row. Is there any way around this behavior? It ruins it all!

Edit (startup 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

An insert trigger is called once for massive inserts, but on a trigger you can use a special inserted table to get all inserted rows.

So imagine you have an insert trigger like this that logs all 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 do a bulk insert on a table , the tableLog filled with the same number of rows that were inserted into the table

For your specific trigger, since you need to call a stored procedure for each line, you need to use the 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 have not tested, but should work

Answer 2

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

It is usually best to insert records into a table from a trigger, and then perform a task that sends emails that run every minute or so, and updates the status of the email to send and adds the sent amount of time to the table when each record is sent. This not only allows you to insert records when emails are unavailable, but also moves the cycle to send each individual letter to a table that users do not access (and, thus, any delay handling of many records will affect only new users, not on others), and this allows you to view the story when you sent an email, which helps when people doubt why they did not receive it. You can also write to the table if the message could not be sent to help identify invalid email addresses.