Início / dba / Perguntas / 332285
Accepted
b_c
Asked: 2023-10-18 23:44:04 +0800 CST

Manter uma coluna ordinal

  • 772

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

3 respostas

  1. Você simplesmente não poderia se preocupar em ter uma coluna bem "ordinal" e apenas criar a tabela assim:

    CREATE TABLE SalesOrderLine (
    LineKey INT IDENTITY(1,1) NOT NULL 
    , SalesOrderKey INT NOT NULL 
    , LineNotes VARCHAR(50)
    , CONSTRAINT PK_Line PRIMARY KEY (SalesOrderKey, LineKey)
    , CONSTRAINT FK_SalesOrderLine_SalesOrder FOREIGN KEY (SalesOrderKey) REFERENCES SalesOrder(SalesOrderKey)
);

    Então, se você quiser exibir "LineNumber" em vez de LineKey, use uma expressão como ROW_NUMBER over (partition by SalesOrderKey, order by LineKey)para gerar o LineNumber em tempo de execução.

    Você não pode "reordenar" as linhas, mas não precisa de um gatilho e tem um índice único e ideal para SalesOrderLine.

    • 1
  2. Best Answer

    Sim, sua intuição está correta, você está fazendo muito trabalho extra. Você atualiza cuidadosamente os números das linhas afetadas e ainda passa por todas elas para renumerá-las como uma etapa de seguro, eu acho. Por que não fazer apenas a etapa de renumeração? Dessa forma, você poderia usar um único gatilho e usar muito menos código. O truque é adicionar chaves de classificação adicionais para garantir que as linhas com números duplicados sejam classificadas corretamente. Você precisa lidar com os seguintes cenários:

    1. novo registro inserido com número de linha especificado
    2. registro atualizado com número de linha diferente
    3. atualizar ou inserir com número de linha nulo
    4. deletando um registro

    para 1) o novo registro enviará todos os registros existentes depois dele - portanto, ao classificar os dups, esse registro precisa vir primeiro

    para 2) o registro atualizado está se movendo para uma nova posição, deslocando outros registros

    • se estiver mudando para uma posição de número mais alto, os outros registros precisarão ser deslocados para baixo, portanto, ao classificar, o registro atualizado precisará ser classificado por último
    • se estiver mudando para uma posição de número inferior, os registros acima precisarão ser deslocados para cima, portanto, ao classificar o registro atualizado, será necessário classificar primeiro

    Para 3) classificamos o nulo por último para que eles obtenham o novo número máximo de linha

    Para 4) as lacunas nos números das linhas serão preenchidas automaticamente pela renumeração

    Isso resulta em um gatilho como segue

    CREATE OR ALTER TRIGGER RenumberLines ON SalesOrderLine
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    IF (ROWCOUNT_BIG() <> 1)
        RETURN;
    
    DECLARE @Line INT
        , @Key INT
        , @LineKey INT
        , @OldLine INT;
    
    SELECT  @Key = SalesOrderKey
        , @OldLine = LineNumber
    FROM deleted;

    SELECT @Key = SalesOrderKey
        , @LineKey = LineKey
        , @Line = LineNumber
    FROM inserted;

    -- For inserts @OldLine will be NULL
    -- For deletes @LineKey will be NULL 

    -- renumber line numbers
    WITH num AS (
        SELECT LineKey id, 
            ROW_NUMBER() OVER (
            ORDER BY 
            ISNULL(LineNumber,9999) -- NULLS sort to bottom
            ,CASE 
            WHEN LineKey <> @LineKey THEN 0             -- non-updated lines
            WHEN LineNumber < ISNULL(@OldLine,9999) THEN -1 -- if prev number was higher sort first
            ELSE 1 END                                  -- else sort last 
            ) ln
        FROM SalesOrderLine
        WHERE SalesOrderKey = @Key
    )
    UPDATE SalesOrderLine
    SET LineNumber = ln
    FROM num
    WHERE LineKey = num.id;
END;
    • 0

  3. Parece que a solução ideal para armazenar esse tipo de informação é utilizar uma hierarchyidcoluna e utilizar o GetDescendant()método quando necessário para inserir linhas na ordem desejada. Isso não precisa de um gatilho e não precisa modificar nenhuma outra linha, mantendo a ordinalidade desejada. O LineNumber pode ser facilmente gerado usando a ROW_NUMBER()função de janela.

    Infelizmente o aplicativo que estou usando não suporta esse tipo de dados, caso contrário eu usaria esta solução; então estou preso usando um gatilho.

    • 0

relate perguntas