title: SQL Server Trigger INSTEAD OF INSERT to Set Timestamps Before Commit
body: <p>In SQL Server 2008 R2, I am looking to create a trigger that imitates the behavior of an Oracle <code>BEFORE INSERT</code> trigger, where any insert that comes in has the trigger update the UPDATE_TS and CREATE_TS to the current timestamp right before the persist.  </p>
  
  <p>To issue I am seeing right now is the error:
    An explicit value for the identity column in table 'MY_TABLE' can only be specified when   a column list is used and IDENTITY_INSERT is ON</p>
  
  <p>I am not sure if it is a good idea to turn SET IDENTITY INSERT table ON and then 
  SET IDENTITY INSERT table OFF within the trigger. Maybe that is a possible solution.</p>
  
  <p>Please advise on best practice.</p>
  
  <p>Example Table is called MY_TABLE:</p>
  
  <pre><code>CREATE TABLE [myschema].[MY_TABLE](
  [MY_TABLE_ID] [bigint] IDENTITY(1,1) NOT NULL,
  [FIELD_TO_UPDATE] [varchar](255) NOT NULL,
  [CREATE_TS] [datetime] NULL,
  [UPDATE_TS] [datetime] NULL),
  PRIMARY KEY (MY_TABLE_ID))
  </code></pre>
  
  <p>Trigger:</p>
  
  <pre><code>CREATE TRIGGER my_table_create_ts_trigger 
  ON [mydb].myschema.MY_TABLE
  INSTEAD OF INSERT
  AS
  BEGIN
      INSERT INTO MY_TABLE([MY_TABLE_ID], [FIELD_TO_UPDATE], [CREATE_TS], [UPDATE_TS])
      SELECT i.MY_TABLE_ID, i.FIELD_TO_UPDATE, GETDATE(), GETDATE()
      FROM INSERTED as i
  END
  </code></pre>