Why does SCOPE_IDENTITY() return NULL after insert on one table and not on the other?

Question

Why does SCOPE_IDENTITY() return NULL after inserting a row in the ComponentAssociation table (as @@IDENTITY returns the right Id) while SCOPE_IDENTITY() returns the right Id after inserting a row in the CustomerProjectAssociation table ?

The two association tables are created the same way.

Here is an extract of the table creation script:

-- 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

And here are two queries executed on the database from SQL Server Management Studio:

INSERT [dbo].[CustomerProjectAssociation]([CustomerId], [ProjectId])
VALUES (1, 2)

SELECT 
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY()

Result:

    RowCount @@IDENTITY SCOPE_IDENTITY
        1         24          24

INSERT [dbo].[ComponentAssociation]([EcuId], [ComponentId])
VALUES(1, 2)

SELECT 
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY()

Result:

    RowCount @@IDENTITY SCOPE_IDENTITY
        1        613        NULL

Using the script you provided, everything run fine on MSS2012. Are you sure this is the table definition that you really have? What version of MSS are you on? — AXMIM, Jul 13 '16 at 13:35
@AXMIM: My comment was too long, so I answered directly to my question to anser you. — Bug Raptor, Jul 13 '16 at 13:57
Same as AXMIM - with these scripts provided, I **cannot** reproduce the problem on SQL Server 2014. It works just fine - as expected — marc_s, Jul 13 '16 at 14:11
@BugRaptor I would have suspected the @@ROWCOUNT first, but if you say it's the scope_identity(), I can't argue. However, there isn't enough information in your question to solve the problem. There is a detail you omitted, because you are probably unaware of it right now. I would suggest you to stop looking into what you have posted here and open your eyes for others things. Also, the proper way to reply would have been to add a "edit" section at the end of the question and refer me to it. Just saying, cause people are very strict on detail like this on this site...for site quality reason. — AXMIM, Jul 13 '16 at 17:30

score 0 · Answer 1 · edited May 23 '17 at 12:18

OK, the issue is solved.

Found the solution here: error when inserting into table having instead of trigger from entity data framework

I added the following select statement at the end of the instead of insert,update trigger returning all the computed columns:

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

Why does SCOPE_IDENTITY() return NULL after insert on one table and not on the other?

1 Answers1