sql 服务器 - 为什么 SCOPE_IDENTITY() 在一个表上插入而不是在另一个表上插入后返回 NULL

Question

为什么SCOPE_IDENTITY()在ComponentAssociation表中插入一行后返回NULL（因为@@IDENTITY返回正确的Id）而SCOPE_IDENTITY()在CustomerProjectAssociation表中插入一行后返回正确的 Id ？

这两个关联表的创建方式相同。

以下是表创建脚本的摘录：

-- Creating table 'CustomerProjectAssociation'
CREATE TABLE [dbo].[CustomerProjectAssociation] 
(
    [Id] int IDENTITY(1,1) NOT NULL,
    [CustomerId] int  NOT NULL,
    [ProjectId] int  NOT NULL,
    [CreationDate] datetime NOT NULL CONSTRAINT DF_CustomerProjectAssociation_CreationDate DEFAULT (SYSUTCDATETIME()),
    [LastModificationDate] datetime NOT NULL CONSTRAINT DF_CustomerProjectAssociation_ModificationDate DEFAULT (SYSUTCDATETIME())
);
GO
-- Creating table 'ComponentAssociation'
CREATE TABLE [dbo].[ComponentAssociation] 
(
    [Id] int IDENTITY(1,1) NOT NULL,
    [EcuId] int  NOT NULL,
    [CreationDate] datetime NOT NULL CONSTRAINT DF_ComponentAssociation_CreationDate DEFAULT (SYSUTCDATETIME()),
    [LastModificationDate] datetime NOT NULL CONSTRAINT DF_ComponentAssociation_ModificationDate DEFAULT (SYSUTCDATETIME()),
    [ComponentId] int  NOT NULL
);
GO
-- Creating primary key on [Id] in table 'CustomerProjectAssociation'
ALTER TABLE [dbo].[CustomerProjectAssociation]
      ADD CONSTRAINT [PK_CustomerProjectAssociation]
          PRIMARY KEY CLUSTERED ([Id] ASC);
GO
-- Creating primary key on [Id] in table 'ComponentAssociation'
ALTER TABLE [dbo].[ComponentAssociation]
      ADD CONSTRAINT [PK_ComponentAssociation]
          PRIMARY KEY CLUSTERED ([Id] ASC);
GO

下面是从SQL Server Management Studio对数据库执行的两个查询：

INSERT [dbo].[CustomerProjectAssociation]([CustomerId], [ProjectId])
VALUES (1, 2)
SELECT 
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY()

结果：

    RowCount @@IDENTITY SCOPE_IDENTITY
        1         24          24
INSERT [dbo].[ComponentAssociation]([EcuId], [ComponentId])
VALUES(1, 2)
SELECT 
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY()

结果：

    RowCount @@IDENTITY SCOPE_IDENTITY
        1        613        NULL

Answer 1

好的，问题解决了。

在这里找到解决方案：插入表时出错，而不是从实体数据框架触发

我在末尾添加了以下 select 语句，而不是返回所有计算列的插入，更新触发器：

select [Id], [CreationDate], [LastModificationDate] from {0}.[dbo].[ComponentAssociation] where @@ROWCOUNT > 0 and Id = scope_identity()