Implementing User-Defined Sort via Trigger

Question

Implementing User-Defined Sort via Trigger

VDT-7677 171

Hi,

Running SQL Server 2008 R2. We have a requirement to implement an arbitrary sort for a table using a numeric sort column. DDL and DML below:

CREATE TABLE [dbo].[Test]

(

[TestID] [bigint] IDENTITY(1,1) NOT NULL,

[Value] [varchar](50) NOT NULL,

[Sort] [numeric](18,2) NOT NULL,

CONSTRAINT

[PK_Test_TestID] PRIMARY KEY CLUSTERED ([TestID] ASC)

WITH

(

PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON

)

ON [PRIMARY]

)

ON [PRIMARY]

GO

INSERT INTO [dbo].[Test]

([Value], [Sort])

VALUES

('Orange', 1),

('Red', 2),

('Purple', 3),

('Grey', 4),

('Yellow', 5),

('White', 6),

('Green', 7),

('Black', 8),

('Blue', 9),

('Pink', 10)

Whenever a sort value for a row is changed we would like the rest of the sort rows to be updated accordingly through the use of a AfterInsert/AfterUpdate trigger (sort = sort + 1 for rows with sort >= inserted.sort). Additionally, we need the trigger to properly handle cases where there may be multiple rows in the trigger's inserted table. How would we achieve this?

Thanks!

1 answer

Your answer

Answer 1

Marcin Policht 63,645 MVP Volunteer Moderator

Try the following (btw. you really should consider an upgrade ;)

CREATE TRIGGER trg_Test_AdjustSort
ON dbo.Test
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Handle only if Sort column was inserted/updated
    -- Determine minimum sort per affected value
    ;WITH SortedInserted AS
    (
        SELECT DISTINCT Sort
        FROM inserted
    )
    UPDATE t
    SET t.Sort = t.Sort + si.Increment
    FROM dbo.Test t
    INNER JOIN
    (
        SELECT si.Sort, COUNT(*) AS Increment
        FROM SortedInserted si
        CROSS APPLY (SELECT COUNT(*) FROM dbo.Test t2 WHERE t2.Sort >= si.Sort) c
        GROUP BY si.Sort
    ) si ON t.Sort >= si.Sort
    WHERE t.TestID NOT IN (SELECT TestID FROM inserted); -- avoid updating the newly inserted/updated rows
END
GO

If the table currently looks like this:

TestID	Value	Sort
1	Orange	1
2	Red	2
3	Purple	3

and you insert:

INSERT INTO dbo.Test (Value, Sort) VALUES ('NewColor', 2);

the result should look like this:

TestID	Value	Sort
1	Orange	1
2	Red	3
3	Purple	4
11	NewColor	2

Note that with large tables, frequent updates can be expensive. Consider indexing Sort.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

VDT-7677 171 Reputation points

2025-10-20T17:10:29.19+00:00

Hi Marcin,

Thanks for the quick response. When I implement the trigger and manually update Red to use a Sort of 3, the higher sorted rows go from 5 to 11. 4 is not present. Thoughts?

Marcin Policht 63,645 MVP Volunteer Moderator

This seems to indicate that the trigger is updating too many rows repeatedly when multiple rows qualify for update. This might be related to the “UPDATE … WHERE Sort >= inserted.Sort” logic running once for each row in inserted, causing cumulative increments instead of a clean one-time shift.

Try the following instead:

CREATE TRIGGER trg_Test_AdjustSort
ON dbo.Test
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Handle upward shifts (insert or move to smaller Sort)
    ;WITH ToShiftUp AS
    (
        SELECT i.Sort
        FROM inserted i
        LEFT JOIN deleted d ON i.TestID = d.TestID
        WHERE d.TestID IS NULL OR i.Sort < d.Sort
    )
    UPDATE t
    SET t.Sort = t.Sort + 1
    FROM dbo.Test t
    INNER JOIN ToShiftUp s ON t.Sort >= s.Sort
    WHERE t.TestID NOT IN (SELECT TestID FROM inserted);

    -- Handle downward shifts (move to larger Sort)
    ;WITH ToShiftDown AS
    (
        SELECT i.Sort, d.Sort AS OldSort
        FROM inserted i
        INNER JOIN deleted d ON i.TestID = d.TestID
        WHERE i.Sort > d.Sort
    )
    UPDATE t
    SET t.Sort = t.Sort - 1
    FROM dbo.Test t
    INNER JOIN ToShiftDown s ON t.Sort <= s.Sort AND t.Sort > s.OldSort
    WHERE t.TestID NOT IN (SELECT TestID FROM inserted);
END
GO

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

VDT-7677 171 Reputation points

2025-10-21T02:14:15.3+00:00

That appears to have corrected the issue. One more thing which I just realized: when new rows are inserted (and a Sort is not provided), the default Sort is -1. Is there a way we can use MAX(Sort) + 1 when the Sort is -1?

Marcin Policht 63,645 MVP Volunteer Moderator

Try the following

CREATE TRIGGER trg_Test_AdjustSort
ON dbo.Test
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -------------------------------------------------------------
    -- 1. Handle new inserts or updates with Sort = -1
    -------------------------------------------------------------
    IF EXISTS (SELECT 1 FROM inserted WHERE Sort = -1)
    BEGIN
        DECLARE @maxSort numeric(18,2);

        SELECT @maxSort = ISNULL(MAX(Sort), 0)
        FROM dbo.Test
        WHERE Sort <> -1;

        ;WITH ToFix AS
        (
            SELECT TestID,
                   ROW_NUMBER() OVER (ORDER BY TestID) AS rn
            FROM inserted
            WHERE Sort = -1
        )
        UPDATE t
        SET t.Sort = @maxSort + rn
        FROM dbo.Test t
        INNER JOIN ToFix f ON t.TestID = f.TestID;
    END

    -------------------------------------------------------------
    -- 2. Handle upward shifts (insert or move to smaller Sort)
    -------------------------------------------------------------
    ;WITH ToShiftUp AS
    (
        SELECT i.Sort
        FROM inserted i
        LEFT JOIN deleted d ON i.TestID = d.TestID
        WHERE (d.TestID IS NULL OR i.Sort < d.Sort)
          AND i.Sort <> -1
    )
    UPDATE t
    SET t.Sort = t.Sort + 1
    FROM dbo.Test t
    INNER JOIN ToShiftUp s ON t.Sort >= s.Sort
    WHERE t.TestID NOT IN (SELECT TestID FROM inserted);

    -------------------------------------------------------------
    -- 3. Handle downward shifts (move to larger Sort)
    -------------------------------------------------------------
    ;WITH ToShiftDown AS
    (
        SELECT i.Sort, d.Sort AS OldSort
        FROM inserted i
        INNER JOIN deleted d ON i.TestID = d.TestID
        WHERE i.Sort > d.Sort
          AND i.Sort <> -1
    )
    UPDATE t
    SET t.Sort = t.Sort - 1
    FROM dbo.Test t
    INNER JOIN ToShiftDown s ON t.Sort <= s.Sort AND t.Sort > s.OldSort
    WHERE t.TestID NOT IN (SELECT TestID FROM inserted);
END
GO

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Akhil Gajavelly 645 Reputation points Microsoft External Staff Moderator

2025-10-23T11:33:43.7833333+00:00

Hi @VDT-7677,

Just following up to see if the suggested trigger solution worked for your SORT adjustments in the TEST table. Were you able to implement it successfully, and did it handle inserts and updates as expected?

If you ran into any edge cases or unexpected behavior, please share the details so we can refine it further

thanks,
Akhil.
VDT-7677 171 Reputation points

2025-10-23T13:27:24.4433333+00:00

Haven't had a chance to implement changes yet, but as soon as I do I will circle back and provide an update. Thanks!
VDT-7677 171 Reputation points

2025-10-23T21:08:55.3266667+00:00

Please see next comment for adjustment.
VDT-7677 171 Reputation points

2025-10-24T15:05:17.44+00:00

I need to amend my previous comment as some key information was not correct, and I have narrowed down the issue further.

Scenario 1:

If (based on original values) Yellow was set to use Sort 4 instead of Sort 5, the changed values should be:

Yellow (4)

Grey (5)

Result: switch works between the two rows, but Sort 6 is not present. White (which should have Sort 6) has Sort 7, and this continues to the end of the set. Last Sort is 11, not 10.

Scenario 2:

If (based on original values) Grey was set to use Sort 5 instead of Sort 4, the changed values should be:

Yellow (4)

Grey (5)

Result: this works correctly.

Scenario 3:

If (based on original values) Orange was set to use Sort 7 instead of Sort 1 the changed values should be:

Red (1)

Purple (2)

Grey (3)

Yellow (4)

White (5)

Green (6)

Orange (7)

Result: this works correctly.

Scenario 4:

If (based on original values) Pink was set to use Sort 2 instead of Sort 10, the changed values should be:

Pink (2)

Red (3)

Purple (4)

Grey (5)

Yellow (6)

White (7)

Green (8)

Black (9)

Blue (10)

Result: this works correctly.

It would seem both the downward and upward shifts function correctly except the one case noted above (single position decrement).

Thoughts?

Share via

Implementing User-Defined Sort via Trigger

1 answer

Your answer