title: SQL Server 2012 AFTER INSERT trigger when inserting multiple rows
body: <p>I'm using the search function in stackoverflow, but I can't find the answer for my problem. But I'm sure I cannot be the first one with this problem.</p>
  
  <p>In my SQL Server 2012 I'm inserting rows and I have an after insert trigger. The inserted pseudo table contains one row with inter alia a timestamp.</p>
  
  <p>With my function the timestamp result a dataset between 1 and x rows, on row for each quarter of an hour. With an cross join i have an multiple row dataset trying to insert into a target table.</p>
  
  <p>To keep in mind:<br>
  With an insert of one row in my source table I fire my trigger. The inserted table contains one row. I try to insert multiple rows in the target table.</p>
  
  <p>Error message: </p>
  
  <blockquote>
    <p>Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.</p>
  </blockquote>
  
  <p>The code</p>
  
  <pre><code>  -- Insert statements for trigger here
  DECLARE @state_time DATETIME,@end_time DATETIME
  SET @state_time =
      (SELECT state_time from inserted)
  SET @end_time =
      (SELECT end_time from inserted)
  
  BEGIN TRY
  
         INSERT INTO t_Duration
            SELECT
      i.interval
      ,t1.call_id
      ,t1.agent_id
      ,t1.service_id
      ,t1.state
      ,i.duration
           FROM
               (SELECT * 
                FROM myFUnction(@state_time, @end_time, 15)) AS i -- here i get more than one row
           CROSS JOIN 
               (SELECT * from inserted) AS t1
  END TRY
  BEGIN CATCH
  END CATCH
  </code></pre>
  
  <p>Why? Is it not possible to insert multiple rows with a trigger? If I copy the <code>INSERT INTO</code> and fake the inserted table with an table variable, the insert execute successful.</p>
  
  <p>I searching a while and it looks like I need a cursor. Is this right. there are alternatives?</p>
  
  <p>Do you have a clue for me?</p>
  
  <p>Thank you
  Frank</p>
  
  <p>=== edit</p>
  
  <p>I should explain what I'm doing...
  I get callrecords with an state, lets say "connect", the call_id and a start timestamp and the endtimestamp.
  i have to report the duration. i wrote a table value function with three parameters start, end and the duration of the interval in minutes.</p>
  
  <p>as a result set, i get an dataset with one row per interval. it contains a interval_is (i crate an unique id for each interval since 1970) and the duration if a call overlapping a interval.</p>
  
  <p>the insertet dataset in the source table:</p>
  
  <pre><code>---------------------------------------------------------------
  | call_id | state | starttime           | endtime             |
  ---------------------------------------------------------------
  | 4711    | 4     | 2013-10-31 09:25:12 | 2013-10-31 09:32:24 |
  ---------------------------------------------------------------
  </code></pre>
  
  <p>the function results the follwing result:</p>
  
  <pre><code>--------------------------
  | interval_id | duration |
  --------------------------
  | 3991333     | 288      |
  --------------------------
  | 3991334     | 144      |
  --------------------------
  </code></pre>
  
  <p>with an cross join a try to insert into target table:</p>
  
  <pre><code>---------------------------------------------
  | interval_id | state | call_id | duration  |
  ---------------------------------------------
  | 3991333     | 4     | 4711    | 288       |
  ---------------------------------------------
  | 3991334     | 4     | 4711    | 144       |
  ---------------------------------------------
  </code></pre>
  
  <p>and it fails.</p>