Как я могу заставить триггер срабатывать для каждой вставленной строки во время INSERT INTO Table (etc) SELECT * FROM Table2?

Триггер вставки вызывается один раз для массовых вставок, но в триггере вы можете использовать специальную таблицу 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

я не проверял, но должно работать

Если вы отправляете электронное письмо, я бы не стал делать это из триггера. Вы действительно хотите, чтобы люди не могли вставлять записи, потому что сервер электронной почты не работает?

Обычно лучше вставлять записи в таблицу из триггера, а затем иметь задание, которое отправляет электронные письма, которое запускается каждую минуту или около того, обновляет статус электронной почты на отправленное и добавляет отправленную дату и время в таблицу при отправке каждой записи. Это не только позволяет вам вставлять записи, когда электронные письма не работают, но и перемещает цикл для отправки каждого отдельного электронного письма в таблицу, к которой пользователи не обращаются (и, таким образом, любая задержка обработки многих записей повлияет только на новых пользователей, а не на кого-либо еще), и это позволяет вам увидеть историю отправки электронного письма, что помогает, когда люди задаются вопросом, почему они не получили его. Вы также можете записать в таблицу, если электронное письмо не удалось отправить, чтобы помочь определить неверные адреса электронной почты.