在创建它的同一个 T-SQL 语句中获取 IDENTITY 值? [英] Get IDENTITY value in the same T-SQL statement it is created in?

问题描述

我被问到是否可以有一个插入语句，它有一个 ID 字段是身份"列，并且分配的值是否也可以插入到同一条记录中的另一个字段中，在同一个插入中声明.

I was asked if you could have an insert statement, which had an ID field that was an "identity" column, and if the value that was assigned could also be inserted into another field in the same record, in the same insert statement.

这可能吗(SQL Server 2008r2)?

Is this possible (SQL Server 2008r2)?

谢谢.

推荐答案

您不能真正做到这一点 - 因为将用于 IDENTITY 列的实际值实际上只是在INSERT 已完成.

You cannot really do this - because the actual value that will be used for the IDENTITY column really only is fixed and set when the INSERT has completed.

但是你可以使用例如一个触发器

You could however use e.g. a trigger

CREATE TRIGGER trg_YourTableInsertID ON dbo.YourTable 
AFTER INSERT
AS 
UPDATE dbo.YourTable
SET dbo.YourTable.OtherID = i.ID
FROM dbo.YourTable t2
INNER JOIN INSERTED i ON i.ID = t2.ID

这会在插入任何行后立即触发，并将 OtherID 列设置为插入行的 IDENTITY 列的值.但严格来说，这不是在同一个陈述中——它只是在你的原始陈述之后.

This would fire right after any rows have been inserted, and would set the OtherID column to the values of the IDENTITY columns for the inserted rows. But it's strictly speaking not within the same statement - it's just after your original statement.

这篇关于在创建它的同一个 T-SQL 语句中获取 IDENTITY 值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！