0

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?

3
  • It seems that sql-server triggers are fired per statement, not per row. I googled and found some solutions, but since I can't test them I don't know if they work or not. Here is one article discussing it: aboutsqlserver.com/2014/01/28/writing-triggers-in-the-right-way – Lennart Aug 30 '16 at 13:22
  • @Lennart Yes, a trigger is fired per statement. It can be verified by executing select * from inserted inside the trigger. – Daniel Marschall Aug 30 '16 at 13:31
  • @Lennart Are you sure this article is discussing this problem? I did not see new information, or information which would help solving the problem. – Daniel Marschall Aug 30 '16 at 14:15
2

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

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.