当前会话的 IDENT_CURRENT 等效项 [英] IDENT_CURRENT equivalent for current session

问题描述

我需要为当前会话获取特定表中最后插入的行.我不能使用 @@IDENTITY 和 SCOPE_IDENTITY() 因为它们将返回任何表的最后插入的标识.IDENT_CURRENT 的问题在于，它将为特定表但为任何会话返回记录的最后插入标识.这对我来说是个问题，因为 INSERT 被多个会话调用，我特别需要我的特定会话的最后插入记录的标识.

I need to get the last inserted row in a specific table for strictly the current session. I can't use @@IDENTITY and SCOPE_IDENTITY() as they will return the last inserted identity for ANY table. The problem with IDENT_CURRENT is that it will return the last inserted identity of a record for a specific table but for ANY session. This is a problem for me because the INSERT is called by multiple sessions, and I specifically need the identity of the last inserted record for my specific session.

任何指示如何完成此操作?

Any pointers how to accomplish this?

ps.INSERT 语句不在 SPROC 中，因此 SPROC 解决方案不可行.

ps. the INSERT statement is not inside a SPROC, so SPROC solutions are not viable.

谢谢

推荐答案

我知道这不是您想要听到的答案，但答案是使用 SCOPE_IDENTITY().您正在考虑的问题(任何 表的位置)是我们使用 SCOPE_IDENTITY() 而不是 @@IDENTITY 的原因.考虑这样一种情况:您有一个带有 IDENTITY 列的表，并且该表上的插入触发器本身插入到带有 IDENTITY 列的表中.

I know it's not the answer you seem to want to hear, but the answer is to use SCOPE_IDENTITY(). The problem you are thinking of (where it will be for any table) is why we use SCOPE_IDENTITY() instead of @@IDENTITY. Consider the case where you have a table with an IDENTITY column, and an insert trigger on that table that itself inserts into a table with an IDENTITY column.

CREATE TABLE dbo.Log(LogID INT IDENTITY(100,1), FooID INT);

CREATE TABLE dbo.Foo(FooID INT IDENTITY(1,1), name VARCHAR(32));
GO

CREATE TRIGGER dbo.Foo_Insert
ON dbo.Foo
FOR INSERT
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.Log(FooID) SELECT FooID FROM inserted;
END
GO

现在，您的情况是您需要一种可靠的方法在插入后检索 ID.SCOPE_IDENTITY() 为您提供，因为它仅限于您的范围，而 @@IDENTITY 不限于您的范围(意味着它将获取最后发出的 IDENTITY，它发生在触发器的范围内，而不是您的范围内:

Now, your situation is that you want a reliable way to retrieve the ID after an insert. SCOPE_IDENTITY() gives you that, since it is restricted to your scope, while @@IDENTITY is not restricted to your scope (meaning it will grab the last IDENTITY issued, which happened in the triggers' scope, not your scope:

INSERT dbo.Foo(name) SELECT 'Bob';

SELECT
  @@IDENTITY,
  SCOPE_IDENTITY();

结果:

----  ----
100   1

请注意，在插入多行的情况下，不应使用 SCOPE_IDENTITY() 和 @@IDENTITY.这样做的方法是使用 OUTPUT 子句.首先让我们放下触发器:

Note that neither SCOPE_IDENTITY() nor @@IDENTITY should be used in the case where you insert multiple rows. The way to do this would be to use the OUTPUT clause. First let's drop the trigger:

DROP TRIGGER dbo.Foo_Insert;

现在让我们测试一个多行插入:

Now let's test a multi-row insert:

  INSERT dbo.Foo(name) 
    OUTPUT inserted.FooID, inserted.name
    SELECT 'Frank' UNION ALL SELECT 'Jim';

结果:

FooID  name
-----  -----
2      Frank
3      Jim

如果您有条件插入，则没有区别.保留我们拥有的表格，让我们尝试两次此代码:

If you have conditional inserts, there is no difference. Keeping the tables we have, let's try this code twice:

DECLARE @table SYSNAME;
SET @table = N'Log';

IF @table = N'Log'
BEGIN
    INSERT dbo.Log(FooID) SELECT 10;
END

IF @table = N'Foo'
BEGIN
    INSERT dbo.Foo(name) SELECT 'Tom';
END

SELECT SCOPE_IDENTITY();

结果:

----
101

让我们用 N'Foo' 再试一次:

Let's try it again with N'Foo':

DECLARE @table SYSNAME;
SET @table = N'Foo';

IF @table = N'Log'
BEGIN
INSERT dbo.Log(FooID) SELECT 10;
END

IF @table = N'Foo'
BEGIN
    INSERT dbo.Foo(name) SELECT 'Tom';
END

SELECT SCOPE_IDENTITY();

结果:

----
4

如果它比这更复杂(例如，您可以插入多个表)，您可以执行以下操作:

If it's more complex than that (e.g. you may insert into more than one table), you can do something like:

IF <some conditional>
BEGIN
    INSERT dbo.sometable ...
    SET @somevar = SCOPE_IDENTITY();
END
IF <some other conditional>
BEGIN
    INSERT dbo.some_other_table ...
    SET @some_other_var = SCOPE_IDENTITY();
END

我不知道为什么你认为这行不通，我也不知道为什么我必须花这么长时间说服你它行得通.同样，如果您展示了一个不起作用的示例(或您认为可能会干扰的任何表格")，我们可能会发表评论.目前看来，您对 SCOPE_IDENTITY() 的看法是基于您对 @@IDENTITY 的了解.这些假设很容易证明或反驳你自己.

I'm not sure why you think this doesn't work, and I'm not sure why I have to go to this length to convince you that it does. Again, if you show an example where this doesn't work (or what "ANY table" you think might interfere), we might be able to comment. As it stands it sounds like your opinion of SCOPE_IDENTITY() is based on something you've heard about @@IDENTITY. These assumptions are quite easy to prove or disprove yourself.

顺便说一句，IDENT_CURRENT 甚至不应在此对话中提及.用于并发活动根本不安全，就我而言，您应该假装您从未听说过它.您还应该为 @@IDENTITY 考虑同样的情况 - 我想不出它的有效用途，除非您确实想从触发器外部捕获 IDENTITY在触发器内生成.

As an aside, IDENT_CURRENT should not even be mentioned in this conversation. It is not safe to use for concurrent activity at all, and you should pretend that you've never heard of it as far as I'm concerned. You should also consider the same for @@IDENTITY - I can't think of a valid use for it unless you really do want to capture, from outside of the trigger, the IDENTITY generated inside a trigger.

这篇关于当前会话的 IDENT_CURRENT 等效项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！