我需要维护一个列,用于存储由父记录外键分区的排序顺序。该列应该是连续的计数数字,并且当一条记录的排序顺序发生更改时,应该自动对记录重新编号。
这是我到目前为止所拥有的:
CREATE TABLE SalesOrder (
SalesOrderKey INT IDENTITY(1,1) NOT NULL
, CONSTRAINT PK_SalesOrder PRIMARY KEY (SalesOrderKey)
, OrderName VARCHAR(50)
);
CREATE TABLE SalesOrderLine (
LineKey INT IDENTITY(1,1) NOT NULL
, CONSTRAINT PK_Line PRIMARY KEY (LineKey)
, SalesOrderKey INT NOT NULL
, CONSTRAINT FK_SalesOrderLine_SalesOrder FOREIGN KEY (SalesOrderKey) REFERENCES SalesOrder(SalesOrderKey)
, LineNumber INT
, LineNotes VARCHAR(50)
);
GO
CREATE TRIGGER NewOrderLine ON SalesOrderLine
AFTER INSERT
AS
BEGIN
IF (ROWCOUNT_BIG() <> 1)
RETURN;
DECLARE @Line INT
, @LineKey INT
, @Key INT;
SELECT @Key = SalesOrderKey
, @LineKey = LineKey
, @Line = LineNumber
FROM inserted;
-- If line number inserted, bump every line after that number
UPDATE SalesOrderLine
SET LineNumber += 1
WHERE LineNumber >= @Line
AND SalesOrderKey = @Key
AND LineKey <> @LineKey;
-- If line number inserted, set it to the last line
UPDATE SalesOrderLine
SET LineNumber = (
SELECT MAX(LineNumber) + 1
FROM SalesOrderLine
WHERE SalesOrderKey = @Key)
WHERE LineKey = @LineKey
AND LineNumber IS NULL;
-- Verify numbers are contiguous
WITH num AS (
SELECT LineKey id,
ROW_NUMBER() OVER (ORDER BY LineNumber) ln
FROM SalesOrderLine
WHERE SalesOrderKey = @Key
)
UPDATE SalesOrderLine
SET LineNumber = ln
FROM num
WHERE LineKey = num.id;
END;
GO
CREATE TRIGGER UpdateLineNumber ON SalesOrderLine
AFTER UPDATE, DELETE
AS
BEGIN
IF (ROWCOUNT_BIG() <> 1)
RETURN;
DECLARE @Line INT
, @Key INT
, @LineKey INT
, @OldLine INT;
SELECT @Line = LineNumber
FROM inserted;
SELECT @Key = SalesOrderKey
, @LineKey = LineKey
, @OldLine = LineNumber
FROM deleted;
-- Record moved up in list
IF @OldLine > @Line
UPDATE SalesOrderLine
SET LineNumber += 1
WHERE LineNumber >= @Line
AND SalesOrderKey = @Key
AND LineKey != @LineKey;
ELSE -- Record moved down in list or deleted
UPDATE SalesOrderLine
SET LineNumber -= 1
WHERE LineNumber BETWEEN @OldLine AND @Line
AND SalesOrderKey = @Key
AND LineKey != @LineKey;
-- Verify numbers are contiguous
WITH num AS (
SELECT LineKey id,
ROW_NUMBER() OVER (ORDER BY LineNumber) ln
FROM SalesOrderLine
WHERE SalesOrderKey = @Key
)
UPDATE SalesOrderLine
SET LineNumber = ln
FROM num
WHERE LineKey = num.id;
END;
这可行,但似乎不是实现这一目标的最佳方法。只使用一个只包含一条语句的触发器是否可行?有一个更好的方法吗?
您可以不用担心有一个漂亮的“序数”列,只需创建如下表:
ROW_NUMBER over (partition by SalesOrderKey, order by LineKey)
然后,如果您想显示“LineNumber”而不是 LineKey,请使用类似于在运行时生成 LineNumber 的表达式。您无法对行进行“重新排序”,但您不需要触发器,并且您拥有 SalesOrderLine 的单个最佳索引。
是的,你的直觉是正确的,你做了很多额外的工作。您仔细更新受影响行的行号,然后您仍然仔细检查所有行以重新编号,我想这是一个保险步骤。为什么不只执行重新编号步骤呢?这样您就可以使用单个触发器并使用更少的代码。诀窍是添加额外的排序键以确保具有重复数字的行正确排序。您需要处理以下场景:
1)新记录将推高其后的所有现有记录 - 因此在对重复项进行排序时,该记录需要排在第一位
2)更新的记录正在移动到新的位置,取代其他记录
对于 3),我们将 null 最后排序,以便它们获得新的最大行号
对于 4) 行号中的空白将通过重新编号自动填充
这会产生一个触发器,如下所示
存储此类信息的理想解决方案似乎是使用列并在需要时
hierarchyid
使用该方法以所需的顺序插入行。GetDescendant()
这不需要触发器,也不需要修改任何其他行,同时保持所需的序数。使用窗口函数可以轻松生成 LineNumberROW_NUMBER()
。不幸的是我正在使用的应用程序不支持这种数据类型,否则我会使用这个解决方案;所以我被困在使用触发器。