当为第一个主键列添加新条目时，如何自动增加表中的第二个主键列

I am trying to find a way to increment a second primary key column in a table automatically when a new entry is added for the first primary key column. I suppose an example would be best here so here goes.

当为第一个主键列添加新条目时，我试图找到一种方法来自动增加表中的第二个主键列。我想这里最好有个例子。

Suppose I have a table:

假设我有一张桌子:

CREATE TABLE T
(
     SecNum INT NOT NULL,
     EntryID INT NOT NULL,
     Value FLOAT,
) CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED 
(
    [SecNum] ASC,
    [EntryID] ASC
)

I would run the following statement:

我将发表以下声明:

INSERT INTO T (SecNum, Value) VALUES (0, 10)

My table should look like:

我的桌子应该是这样的:

SECNUM | ENTRYID | VALUE
-------------------------
   0         0       10

I would run the following statement:

我将发表以下声明:

INSERT INTO T (SecNum, Value) VALUES (0, 10)

My table should look like:

我的桌子应该是这样的:

SECNUM | ENTRYID | VALUE
-------------------------
   0         0       10
   0         1       10

I would run the following statement:

我将发表以下声明:

INSERT INTO T (SecNum, Value) VALUES (1, 20)

My table should look like:

我的桌子应该是这样的:

SECNUM | ENTRYID | VALUE
-------------------------
   0         0       10
   0         1       10
   1         0       20

4 个解决方案

#1

This is possible using an INSTEAD OF trigger:

这是有可能的使用一个而不是触发器:

CREATE TRIGGER TriggerName
ON T
INSTEAD OF INSERT
AS
    -- THIS TOP BIT IS OPTIONAL, IT WILL ALLOW ENTRY ID TO BE OVERRIDDEN IF 
    -- IT IS SUPPLIED TO THE INSERT AND WILL NOT VIOLATE THE PRIMARY KEY
    IF NOT EXISTS 
        (   SELECT  1
            FROM    T
                    INNER JOIN inserted i
                        ON i.SecNum = T.secNum
                        AND i.EntryID = T.EntryID
            UNION
            SELECT  1
            FROM    inserted
            WHERE   EntryID IS NULL
        )
        BEGIN
            INSERT T (SecNum, EntryID, Value)
            SELECT  SecNum, EntryID, Value
            FROM    inserted
        END
    ELSE
    -- IF OVERRIDE ABILITY IS NOT REQUIRED JUST USE THE BELOW INSERT
        BEGIN
            INSERT T (SecNum, EntryID, Value)
            SELECT  i.SecNum, COALESCE(LastID, 0), i.Value
            FROM    inserted I
                    LEFT JOIN 
                    (   SELECT  SecNum, MAX(T.EntryID) + 1 [LastID]
                        FROM    T
                        GROUP BY SecNum
                    ) T
                        ON T.SecNum = i.SecNum

        END

Example here

例子

HOWEVER this is not very elegant. It could be worth asking is it really necessary? Could you get away with using a surrogate primary key, and use ROW_NUMBER() to create Entry ID's on the fly?

然而，这并不是很优雅。值得问的是，这真的有必要吗?您是否可以使用代理主键，并使用ROW_NUMBER()来创建条目ID ?

#2

How about something like this:

像这样的东西怎么样:

INSERT INTO T (SecNum, Value, EntryId)
SELECT 0, 10, count(*)
FROM T WHERE SecNum = 0

It is not the cleanest solution and will perform pretty poorly too. But it should get the job done.

它不是最干净的解决方案，也会表现得很糟糕。但它应该能完成任务。

#3

This is how to do it without storing the value in the table (I'm not sure why you want to store it)

这是如何在不存储表中的值的情况下实现它(我不知道为什么要存储它)

TABLE

表

DECLARE @T TABLE
    (
      SecNum INT NOT NULL,
      EntryID INT,
      Value FLOAT
    )

DATA

数据

INSERT  INTO @T
        ( SecNum, Value )
VALUES  ( 0, 10 )
INSERT  INTO @T
        ( SecNum, Value )
VALUES  ( 0, 10 )
INSERT  INTO @T
        ( SecNum, Value )
VALUES  ( 1, 20 )

QUERY

查询

SELECT  SecNum,
        ROW_NUMBER() OVER ( PARTITION BY value ORDER BY Value ) - 1 AS EntryID,
        Value
FROM    @T

RESULT

结果

SecNum  EntryID Value
0          0    10
0          1    10
1          0    20

If the EntryID changes with SecNum AND Value use this query:

如果EntryID与SecNum和Value一起更改，请使用此查询:

SELECT  SecNum,
        ROW_NUMBER() OVER ( PARTITION BY Value,SecNum ORDER BY Value, SecNum ) - 1 AS EntryID,
        Value
FROM    @t

RESULT 2

结果2

SecNum  EntryID Value
0           0   10
0           1   10
1           0   10
1           0   20

#4

Your problem can be solved by using an instead of insert trigger

您的问题可以通过使用而不是插入触发器来解决

 create trigger Trigger1 on T INSTEAD OF INSERT
 as
 begin                                  
       insert into T(SecNum,EntryID,Value)
       select SecNum,
             (select count(*) from T where SecNum = i.SecNum) as EntryID, 
             value 
       from inserted i
 end