Maintain an ordinal column

Question

I need to maintain a column that stores a sort order partitioned by a parent record foreign key. The column should be the contiguous counting numbers and should automatically renumber the records when the sort order is changed for one record.

Here's what I have so far:

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;

This works, but doesn't seem like the best way to accomplish this. Is it feasible to use only one trigger containing only one statement? Is there a better way to do this?

Answer 1

Yes your intuition is correct, you are doing a lot of extra work. You carefully update the line numbers of the affected lines and then you still go through them all to renumber them as an insurance step I guess. Why not just do the renumber step only? That way you could use a single trigger and use way less code. The trick is to add additional sort keys to make sure the lines with duplicate numbers are sorted properly. You need to handle the following scenarios:

1. new record inserted with specified line number
2. updated record with different line number
3. update or insert with null line number
4. deleting a record

for 1) the new record will push up any existing records after it - so when sorting the dups this record needs to come first

for 2) the updated record is moving to a new position displacing other records

• if moving to a higher number position, then other records need to shift down so when sorting the updated record needs to sort last
• if moving to a lower number position, the records above need to shift up so when sorting the updated record needs to sort first

For 3) we sort the null last so they get new max line number

For 4) the gaps in line numbers will get automatically filled by the renumbering

This results in one trigger as follows

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;

Answer 2

You could just not worry about having a pretty "ordinal" column and just create the table like this:

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)
);

Then if you want to display "LineNumber" instead of LineKey, use an expression like ROW_NUMBER over (partition by SalesOrderKey, order by LineKey) to generate the LineNumber at runtime.

You can't "reorder" the Lines, but you don't need a trigger, and you have a single, optimal index for SalesOrderLine.

Answer 3

It seems that the ideal solution to store this type of information is to use a hierarchyid column and use the GetDescendant() method when needed to insert lines in the desired order. This does not need a trigger and does not need to modify any other rows while maintaining desired ordinality. The LineNumber can be easily generated by using the ROW_NUMBER() window function.

Unfortunately the app I am using does not support this data type, otherwise I would use this solution; so I am stuck using a trigger.