Help needed with "Cannot insert explicit value for identity column" Error

I have a table Table1 on which I have created an insert/update trigger. This trigger insert records of inserts/updates on Table1 into Table1_history. To test whether the trigger is inserting records into Table1_history, I have inserted some test data into Table1 and I am receivi开发者_开发百科ng the error:

Cannot insert explicit value for identity column in table 'Table1_history' when IDENTITY_INSERT is set to OFF  

Schema of Table1 and Table1_history are identical except an additional 'inserted_on_date' field in Table1_history.

I have set the Identity Insert to ON and the trigger works. But do I have to do this every time I need to insert a new record in Table1?

Put the IDENTITY INSERT ON into the trigger itself, it only needs to be active while you are pushing values into table1_history, because that is where the problem is.

Or, in Table1_history, don't make the pk field an IDENTITY, it does not need to be because you are supplying the values from Table1.

I assume your 'inserted_on_date' field is a datetime. You should be able to turn off the identity insert and set the default value for this column to GETDATE(), which will set any newly inserted records to the datetime the server has when the record is inserted.

You don't mention what database but I assume it's SQL Server 2008.

The set identity on command is session specific. So yes, you need to issue it each time.

Instead, you should remove the identity specification from the history table. You don't need it.