Scope_Identity（）之后的审计跟踪插入时出现SQL错误

I'm trying to use a stored procedure to do an insert on a main table, then also insert that data into a historical data table. I don't want to use a trigger, but rather just do a simple insert of all columns into the history table. I am getting this error, however.

我正在尝试使用存储过程在主表上执行插入，然后还将该数据插入到历史数据表中。我不想使用触发器，而只是将所有列的简单插入到历史表中。但是，我收到了这个错误。

"An explicit value for the identity column in table 'ipaudittrail' can only be specified when a column list is used and the IDENTITY_INSERT is on."

“只有在使用列列表且IDENTITY_INSERT打开时，才能指定表'ipaudittrail'中标识列的显式值。”

I imagine this has to do with Scope_Identity()? but I'm not well-versed in SQL, so I'm not really sure the best way to resolve this. Any suggestions are appreciated. Thanks!

我想这与Scope_Identity（）有关？但我并不精通SQL，所以我不确定解决这个问题的最佳方法。任何建议表示赞赏。谢谢！

Insert Input
  (OpenedByName, Owner, Description, WorkOfDate)
Values 
  (@vOpenedByFirstName, @vOwner, @vDescription, @vWorkOfDate)

Set @vRecID = Scope_Identity()

Insert InputAuditTrail 
Select * 
  From Input 
 Where RecID = @vRecID

Select * 
  From Input     
 Where i.RecID = @vRecID

2 个解决方案

#1

Your audit trail table shouldn't have the identity property set on its RecID column as you don't want it to auto increment independently. You would need to alter the table definition to reflect that.

您的审计跟踪表不应在其RecID列上设置标识属性，因为您不希望它独立地自动增加。您需要更改表定义以反映这一点。

Having done that you could use the OUTPUT clause for this.

完成后，您可以使用OUTPUT子句。

Insert Input(OpenedByName,Owner,Description,WorkOfDate)
OUTPUT inserted.* INTO InputAuditTrail /*Inserts to Audit Table*/
OUTPUT inserted.* /*Returns to Client*/
Values (@vOpenedByFirstName,@vOwner,@vDescription,@vWorkOfDate)

#2

Insert InputAuditTrail Select * From Input Where RecID = @vRecID

This returning a a number of columns and one of them is attempting to write into InputAuditTrail's identity column.

这会返回一些列，其中一列正在尝试写入InputAuditTrail的标识列。

Replace the statement with

将语句替换为

Insert InputAuditTrail(col1, col2, ./...) Select cola, colb, .... From Input Where RecID = @vRecID

#1