Preciso manter uma coluna que armazene uma ordem de classificação particionada por uma chave estrangeira de registro pai. A coluna deve conter os números de contagem contíguos e renumerar automaticamente os registros quando a ordem de classificação for alterada para um registro.
Aqui está o que tenho até agora:
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;
Isso funciona, mas não parece ser a melhor maneira de conseguir isso. É viável usar apenas um gatilho contendo apenas uma instrução? Existe uma maneira melhor de fazer isso?
