SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Operation (
    id INT,
    change INT,
    previousId INT,
    previousAmount INT,
    newAmount INT
)
CREATE Procedure UpdateChain
    @StartingId INT
AS
    WITH CTE_Operation AS (
        -- Any root operation, when  @StartingId IS NULL
        SELECT
            O.*,
            previousAmountCalc = NULL,
            newAmountCalc = O.change
        FROM Operation O
        WHERE @StartingId IS NULL AND O.previousId IS NULL
        UNION ALL
        -- Specific operation row, when  @StartingId IS NOT NULL
        SELECT -- Initial explicitly selected operation
            O.*,
            previousAmountCalc = OP.newAmount,
            newAmountCalc = ISNULL(OP.newAmount, 0) + O.change
        FROM Operation O
        LEFT JOIN Operation OP ON OP.id = O.previousId
        WHERE O.id = @StartingId
        UNION ALL
        -- Recursively follow the links
        SELECT
            O.*,
            previousAmountCalc = C.newAmountCalc,
            newAmountCalc = C.newAmountCalc + O.change
        FROM CTE_Operation C
        JOIN Operation O ON O.previousId = C.id
    )
    UPDATE O
    SET
        previousAmount = previousAmountCalc,
        newAmount = newAmountCalc
    FROM CTE_Operation C
    JOIN Operation O ON O.id = C.id

-- End of procedure
INSERT Operation (id, change, previousId)
VALUES
    (1, 1, null),
    (2, 2, 1),
    (3, 3, 2),
    (4, 4, 3)

-- Refresh all (starting with OPerations with previousId = NULL)
EXEC UpdateChain NULL

SELECT * FROM Operation O ORDER BY O.newAmount


id
change
previousId
previousAmount
newAmount


1
1
null
null
1

2
2
1
1
3

3
3
2
3
6

4
4
3
6
10


UPDATE Operation
SET Change = 10
WHERE id = 1

-- Refresh everything from id = 1 and down the chain
EXEC UpdateChain 1

SELECT * FROM Operation O ORDER BY O.newAmount


id
change
previousId
previousAmount
newAmount


1
10
null
null
10

2
2
1
10
12

3
3
2
12
15

4
4
3
15
19


-- Insert new operation after id = 2
INSERT Operation (id, change, previousId)
VALUES (5, 5, 2)

-- Adjust the backlink for the following operation to point to the new entry
UPDATE Operation
SET previousId = 5
WHERE id = 3

-- Refresh everything from the prior id = 2 and down the chain
EXEC UpdateChain 2

SELECT * FROM Operation O ORDER BY O.newAmount


id
change
previousId
previousAmount
newAmount


1
10
null
null
10

2
2
1
10
12

5
5
2
12
17

3
3
5
17
20

4
4
3
20
24