Auto increment primary key inside a number range

Question

I have following table in SQL Server 2012:

CREATE TABLE [dbo].[xxx](
    [K1] [int] NOT NULL,
    [K2] [int] NOT NULL,
    [F1] [int] NULL,
    [F2] [int] NULL,
    [F3] [int] NULL,
 CONSTRAINT [PK_xxx] PRIMARY KEY CLUSTERED 
(
    [K1] ASC,
    [K2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

K1 and K2 are primary keys.

I want to achieve that K2 is automatically incremented inside the number range of K1.

An usual query should look like:

INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)

A trigger should now check the highest K2 inside the number range of K1 (select MAX(K2) from xxx where K1 = i.K1) , and add one to it.

I tried following trigger:

CREATE TRIGGER [dbo].[trg_xxx_INSERT] ON [dbo].[xxx] INSTEAD OF INSERT AS 
BEGIN 
SET NOCOUNT ON 

insert into xxx
 (K1, K2, F1, F2, F3) 
select 
  i.K1, (select isnull(max(K2),0)+1 from xxx where K1 = i.K1), i.F1, i.F2, i.F3
from
  inserted i;
END

It does work well if an INSERT statement adds single rows.

truncate table xxx
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)

In this case, I will have following rows in the Table xxx:

10, 1
10, 2
10, 3

However, the trigger fails if I want to add more than 1 row:

truncate table xxx
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...), (10, -1, ...), (10, -1, ...)

I guess the subquery is only executed once and not for every i, and therefore I get a primary key violation.

How can I achive that the trigger also works with multiple inserts?

Answer 1

This example should get you what you need, or at least it worked in my testing.

Setup

CREATE TABLE dbo.TestInsert 
    (
    TestID INT NOT NULL
    , TestSubID INT NOT NULL
    , TestValue VARCHAR(100) NOT NULL
    );

GO

CREATE TRIGGER dbo.trgTestInsert ON dbo.TestInsert INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON

INSERT INTO dbo.TestInsert 
(TestID, TestSubID, TestValue)
SELECT I.TestID
    , NewSubID = ISNULL(T1.MaxID, 0) + ROW_NUMBER() OVER (PARTITION BY I.TestID ORDER BY I.TestID)
    , I.TestValue 
FROM INSERTED AS I
    LEFT OUTER JOIN (
                    SELECT T.TestID
                        , MAX(T.TestSubID) AS MaxID
                    FROM TestInsert AS T
                    GROUP BY T.TestID
                    ) AS T1 ON T1.TestID = I.TestID;

END

GO

Testing

INSERT INTO dbo.TestInsert
(TestID, TestSubID, TestValue)
VALUES (10, NULL, 'test 1')
    ,(10, NULL, 'Test 2')
    ,(11, NULL, 'Test 3') 

SELECT TestID, TestSubID, TestValue FROM dbo.TestInsert;