title: SCOPE_IDENTITY And Instead of Insert Trigger work-around
body: <p>OK, I have a table with no natural key, only an integer identity column as it's primary key. I'd like to insert and retrieve the identity value, but also use a trigger to ensure that certain fields are always set. Originally, the design was to use instead of insert triggers, but that breaks scope_identity. The output clause on the insert statement is also broken by the instead of insert trigger. So, I've come up with an alternate plan and would like to know if there is anything obviously wrong with what I intend to do:</p>
  
  <p>begin contrived example:</p>
  
  <pre><code>    CREATE TABLE [dbo].[TestData] (
      [TestId] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
      [Name] [nchar](10) NOT NULL)
  
      CREATE TABLE [dbo].[TestDataModInfo](
      [TestId] [int] PRIMARY KEY NOT NULL,
      [RowCreateDate] [datetime] NOT NULL)
  
      ALTER TABLE [dbo].[TestDataModInfo]  WITH CHECK ADD  CONSTRAINT
      [FK_TestDataModInfo_TestData] FOREIGN KEY([TestId])
      REFERENCES [dbo].[TestData] ([TestId]) ON DELETE CASCADE
  
  CREATE TRIGGER [dbo].[TestData$AfterInsert]
     ON [dbo].[TestData]
     AFTER INSERT
  AS 
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      INSERT INTO [dbo].[TestDataModInfo]
             ([TestId],
              [RowCreateDate])
          SELECT
              [TestId],
              current_timestamp
          FROM inserted
  
      -- Insert statements for trigger here
  
  END
  </code></pre>
  
  <p>End contrived example.</p>
  
  <p>No, I'm not doing this for one little date field - it's just an example.</p>
  
  <p>The fields that I want to ensure are set have been moved to a separate table (in TestDataModInfo) and the trigger ensures that it's updated. This works, it allows me to use scope_identity() after inserts, and appears to be safe (if my after trigger fails, my insert fails). Is this bad design, and if so, why?</p>