Início / user-280777

b_c's questions

Martin Hope
b_c
Asked: 2023-10-18 23:44:04 +0800 CST
  • 6

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?

sql-server