我需要维护一个列,用于存储由父记录外键分区的排序顺序。该列应该是连续的计数数字,并且当一条记录的排序顺序发生更改时,应该自动对记录重新编号。
这是我到目前为止所拥有的:
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;
这可行,但似乎不是实现这一目标的最佳方法。只使用一个只包含一条语句的触发器是否可行?有一个更好的方法吗?