title: How can I get a trigger to fire on each inserted row during an INSERT INTO Table (etc) SELECT * FROM Table2?
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>INSERT INTO Table (ID, User, AssignDate, LastActionDate)
      SELECT 
          ID
          ,User
          ,GETDATE() [AssignDate]
          ,GETDATE() [LastModifiedDate]
      FROM Table2
          /*snip*/
  </code></pre>
  
  <p>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!</p>
  
  <p>Edit (trigger code):</p>
  
  <pre><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
  </code></pre>