sql-server – 当@@ IDENTITY没有时,SCOPE_IDENTITY如何返回null

SCOPE_IDENTITY返回null,但@@ IDENTITY不返回.

我不明白这是怎么可能的.

你能想到这样的原因吗？

解决方法

insert into a table with no identity,
that table has an insert trigger that
then inserts into a history table with
an identity. SCOPE_IDENTITY() will be
null (no identity in the local scope),
but @@IDENTITY will report the
identity from the trigger.

FYI,有一个已知的错误SCOPE_IDENTITY()：https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811

您最好的使用身份验证是使用OUTPUT INTO,它可以捕获一组ID,并且不受SCOPE_IDENTITY()错误的限制：

declare @x table (tableID int identity not null primary key,datavalue varchar(10))
declare @y table (tableID int,datavalue varchar(10))

INSERT INTO @x values ('aaaa')
INSERT INTO @x values ('bbbb')
INSERT INTO @x values ('cccc')
INSERT INTO @x values ('dddd')
INSERT INTO @x values ('eeee')


INSERT INTO @x
    (datavalue)
    OUTPUT INSERTED.tableID,INSERTED.datavalue     --<<<<OUTPUT INTO SYNTAX
    INTO @y                                         --<<<<OUTPUT INTO SYNTAX
SELECT
    'value='+CONVERT(varchar(5),dt.NewValue)
    FROM (SELECT id as NewValue from sysobjects where id<20) dt
    ORDER BY dt.NewValue


select * from @x
select * from @y

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!