title: How to get a table identity inserted by instead of insert trigger?
body: <p>I have a problem described as follows: I have a table with one instead of insert trigger:</p>
  
  <pre><code>create table TMessage (ID int identity(1,1), dscp varchar(50))
  GO
  Alter trigger tr_tmessage on tmessage
  instead of insert
  as
  
  --Set NoCount On
  
    insert into tmessage
    select dscp from inserted
  GO
  Alter proc P1
  As
  
  --Set NoCount On
  
  insert into tmessage
  (dscp)
  values('some data')
  
  Select SCOPE_IDENTITY()
  
  GO
  </code></pre>
  
  <p>When I execute P1 it returns Null for SCOPE_IDENTITY() instead of the identity of the table. I even tried Output clause in the insert statement in the proc. but again the output table Identity field that gets filled from inserted in the Output clause is 0 in this case.</p>
  
  <p>Any help would be appreciated. </p>