Maintain an ordinal column

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?