需要有关“无法为标识列插入显式值”的帮助错误
我有一个表 Table1，在其中创建了插入/更新触发器。此触发器将 Table1 上的插入/更新记录插入到 Table1_history 中。为了测试触发器是否将记录插入到 Table1_history 中，我已将一些测试数据插入到 Table1 中，但收到错误：
Cannot insert explicit value for identity column in table 'Table1_history' when IDENTITY_INSERT is set to OFF
Table1 和 Table1_history 的架构相同，除了 Table1_history 中的附加“inserted_on_date”字段。
我已将“身份插入”设置为“开”并且触发器工作。但是每次需要在 Table1 中插入新记录时都必须这样做吗？
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 receiving 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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
将 IDENTITY INSERT ON 放入触发器本身，它只需要在将值推入 table1_history 时处于活动状态，因为这就是问题所在。
或者，在 Table1_history 中，不要将 pk 字段设为 IDENTITY，这不需要如此，因为您提供的是 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.
我假设您的“inserted_on_date”字段是日期时间。您应该能够关闭身份插入并将此列的默认值设置为
GETDATE()，这会将任何新插入的记录设置为插入记录时服务器具有的日期时间。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.您没有提到什么数据库，但我假设它是 SQL Server 2008。
命令上的设置标识是特定于会话的。所以是的，您每次都需要发出它。
相反，您应该从历史表中删除身份规范。你不需要它。
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.