-2

This is my table:

CREATE TABLE [MyTable]
(
    [GroupId]     INT        NOT NULL,
    [ItemId]      INT        NOT NULL,
    [Data]        INT        NOT NULL,
    PRIMARY KEY CLUSTERED ([GroupId], [ItemId]),
    CONSTRAINT [FK_MyTable_tblGroup] FOREIGN KEY ([GroupId]) REFERENCES [tblGroup] ([Id])
);

I used this trigger to generate ItemID on insert:

CREATE TRIGGER [Trigger_MyTable_OnInsert]
ON [MyTable]
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO MyTable([GroupId], [ItemId], [Data])
    SELECT [GroupId],
        ISNULL((SELECT MAX(ItemId)
                FROM MyTable
                WHERE MyTable.GroupId = GroupId), 0) + 1,
        [Data]
    FROM  inserted i
END

At insert time, the new ItemId value is inserted into the table but is not displayed:

inserted row

This problem also exists when working with the Entity Framework and I found the problem with this part of the database.

I'm trying to get an ItemId sequence for each GroupId. ItemId is unique to each group, not the table, that's why I can't use IDENTITY.

0
4

To answer the question directly, the problem is:

WHERE MyTable.GroupId = GroupId

You haven't specified which GroupId you mean on the right hand side of that expression.

The SQL standard dictates that SQL Server must resolve it to the innermost scope, so you have effectively written:

WHERE MyTable.GroupId = MyTable.GroupId

You should instead write:

WHERE MyTable.GroupId = i.GroupId

db<>fiddle demo

The trigger has other problems, such as not handling multi-row inserts properly, and not being safe under concurrency.

A related article of mine: Sequence Tables.

0

Finally i found the point that i have to use OUTPUT command.

Using OUTPUT in an INSTEAD OF trigger

This is Fixed SQL Trigger:

CREATE TRIGGER [Trigger_MyTable_OnInsert]
ON [MyTable]
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO MyTable([GroupId], [ItemId], [Data])
    OUTPUT INSERTED.[ItemId] as [ItemId]
    SELECT [GroupId],
        ISNULL((SELECT MAX(ItemId)
                FROM MyTable
                WHERE MyTable.GroupId = GroupId), 0) + 1,
        [Data]
    FROM  inserted i
END

And as I said, this problem exists when using EntityFrameworkCore. To solve this problem, we must tell Entity Framework that this value will change after call the Add() method.

To solve this problem, it is enough to add the following method in the DbContext class:

entity.Property(table => table.ItemId).ValueGeneratedOnAdd();

To be clear, the result of the code is as follows:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MyTable>(entity =>
    {
        entity.HasKey(e => new { e.GroupId, e.ItemId });
        entity.Property(table => table.ItemId).ValueGeneratedOnAdd();
        entity.ToTable("MyTable");
    });
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.