在这种情况下，SCOPE_IDENTITY能够正常工作吗？

Question

我有一个自增的PK键，需要将记录插入数据库并获取该PK，然后在另一个插入中使用它。但我希望在一个事务中完成此操作，这是否可行？我的想法是，如果任何更新/插入失败，我必须回滚所有操作，但我认为需要提交。起初我打算在ado.net中执行此操作，但后来改用存储过程，因为我认为这可能会解决这个问题。在这种情况下，存储过程能帮助我吗？

- chobo2

Answer 1

是的，scope_identity将会给你最新插入的id。作为替代方案，如果你使用sql server 2005+，你可以使用output clause。

INSERT INTO [MyTable]([MyCol])
OUTPUT INSERTED.ID
SELECT [MyCol] FROM [MySourceTable];

- Paul Fleming

Answer 2

如何呢：

BEGIN TRANSACTION
BEGIN TRY

   INSERT INTO dbo.YourFirstTable(.....)
   VALUES(.......)

   DECLARE @newID INT
   SELECT @newID = SCOPE_IDENTITY()

   INSERT INTO dbo.YourSecondTable(ID, .......)
   VALUES(@newID, ........)

   COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    ROLLBACK TRANSACTION
END CATCH

应该在任何版本的SQL Server 2005或更新版本中运行。

仅通过获取SCOPE_IDENTITY()值，您绝对不会“破坏”事务...将其包装到存储过程中，或者直接从调用代码中调用它。

- marc_s

---

@chobo2：将其放入存储过程中并调用该过程-这是最简单的解决方案... - marc_s

输出会破坏事务吗？看起来记录已经在那个时候被插入了。 - chobo2

@chobo2: 不是的 - 为什么你认为每件事都会中断一个事务？除非发生ROLLBACK操作 - 否则没有任何东西会"中断"事务..... - marc_s

你需要提交记录才能插入并获取生成的主键。正如我所说，我使用nhibernate。要获取生成的主键，记录必须被提交，并且这需要在服务器上完成。一旦提交发生，该事务就完成了，您必须开始一个新事务。因此会中断事务。 - chobo2

@chobo2：不会 - 数据将被插入 - 只是还没有提交。但是，当它被插入时，新的IDENTITY值被定义 - 您可以读取这些值。现在，如果事务最终被回滚，那么这些PK标识值将被“遗忘”（例如，它们已被使用，但被回滚），这就是在IDENTITY列中出现间隙的原因。但是，您绝对需要阅读有关SQL Server中的事务和事务处理的相关MSDN文档。 - marc_s