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