SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE FUNCTION ValidateOperationSeries (
    @AccountNo INT
) RETURNS BIT -- 1 = valid, 0 = invalid
AS
BEGIN
    DECLARE @MinimumAmount NUMERIC(19,2) = (
        SELECT MIN(CummulativeAmount)
        FROM (
            SELECT SUM(O.Change) OVER(ORDER BY O.OperationId) AS CummulativeAmount
            FROM Operation O
            WHERE O.AccountNo = @AccountNo
        ) S
    )
             
    DECLARE @IsValid BIT = CASE WHEN @MinimumAmount < 0 THEN 0 ELSE 1 END

    RETURN @IsValid
END
CREATE TABLE Operation (
    OperationId INT,
    AccountNo INT,
    Change NUMERIC(19,2),
    SomeOtherValue VARCHAR(100)
)
CREATE TRIGGER TR_Operation_ValidateOperationSeries
    ON Operation 
    AFTER INSERT, UPDATE, DELETE
AS
    DECLARE @FailedAccountNo INT = (
        SELECT TOP 1 A.AccountNo
        FROM (
            -- Get affected account numbers where some key data has changed
            SELECT DISTINCT A.AccountNo
            FROM INSERTED I
            FULL OUTER JOIN DELETED D ON D.OperationId = I.OperationId
            CROSS APPLY (
                SELECT I.AccountNo WHERE I.AccountNo IS NOT NULL
                UNION ALL
                SELECT D.AccountNo WHERE D.AccountNo IS NOT NULL
            ) A
            WHERE (
                D.OperationId IS NULL    -- Any inserted row
                OR I.OperationId IS NULL -- Any deleted row
                OR UPDATE(AccountNo)     -- PARTITION BY key changed
                OR UPDATE(OperationId)   -- The ORDER BY column changed
                OR UPDATE(Change)        -- Tracked value changed
            )
        ) A
        WHERE dbo.ValidateOperationSeries(A.AccountNo) = 0 -- Validation Fails
    )

    IF @FailedAccountNo IS NOT NULL
    BEGIN
        DECLARE @ErrorMessage VARCHAR(1000) =
            CONCAT('Operation series failed validation for account ',
                    @FailedAccountNo,
                   ' in TR_Operation_ValidateOperationSeries.');
        THROW 50000, @ErrorMessage, 1
    END

-- End of trigger
INSERT Operation (OperationId, AccountNo, Change)
VALUES
    (1, 100, 1),
    (2, 100, 2),
    (3, 100, 3),
    (4, 100, 4),
    (5, 200, 11),
    (6, 200, 22),
    (7, 200, 33),
    (8, 200, 44)

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O ORDER BY O.AccountNo, O.OperationId


OperationId
AccountNo
Change
SomeOtherValue
CummulativeAmount


1
100
1.00
null
1.00

2
100
2.00
null
3.00

3
100
3.00
null
6.00

4
100
4.00
null
10.00

5
200
11.00
null
11.00

6
200
22.00
null
33.00

7
200
33.00
null
66.00

8
200
44.00
null
110.00


-- Succeeds because all cummulative sums remain positive
UPDATE Operation
SET Change = -5
WHERE OperationId = 4

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId


OperationId
AccountNo
Change
SomeOtherValue
CummulativeAmount


1
100
1.00
null
1.00

2
100
2.00
null
3.00

3
100
3.00
null
6.00

4
100
-5.00
null
1.00

5
200
11.00
null
11.00

6
200
22.00
null
33.00

7
200
33.00
null
66.00

8
200
44.00
null
110.00


-- *** FAILS *** because although th ecummulative sum for the current row
-- remains positive, the sum for row id=4 would now be negative
UPDATE Operation
SET Change = 1
WHERE OperationId = 3

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId
Msg 50000 Level 16 State 1 Line 34
Operation series failed validation for account 100 in TR_Operation_ValidateOperationSeries.

-- Succeeds because all cummulative sums remain positive
UPDATE Operation
SET Change = -3
WHERE OperationId = 4

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId


OperationId
AccountNo
Change
SomeOtherValue
CummulativeAmount


1
100
1.00
null
1.00

2
100
2.00
null
3.00

3
100
3.00
null
6.00

4
100
-3.00
null
3.00

5
200
11.00
null
11.00

6
200
22.00
null
33.00

7
200
33.00
null
66.00

8
200
44.00
null
110.00


-- This is now valid, because all cummulative sums (including id=4) remain positive
UPDATE Operation
SET Change = 1
WHERE OperationId = 3

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId


OperationId
AccountNo
Change
SomeOtherValue
CummulativeAmount


1
100
1.00
null
1.00

2
100
2.00
null
3.00

3
100
1.00
null
4.00

4
100
-3.00
null
1.00

5
200
11.00
null
11.00

6
200
22.00
null
33.00

7
200
33.00
null
66.00

8
200
44.00
null
110.00


-- For a different account, add a record where the sum does not quite go negative
INSERT Operation (OperationId, AccountNo, Change)
VALUES
    (9, 200, -110)

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O ORDER BY O.AccountNo, O.OperationId


OperationId
AccountNo
Change
SomeOtherValue
CummulativeAmount


1
100
1.00
null
1.00

2
100
2.00
null
3.00

3
100
1.00
null
4.00

4
100
-3.00
null
1.00

5
200
11.00
null
11.00

6
200
22.00
null
33.00

7
200
33.00
null
66.00

8
200
44.00
null
110.00

9
200
-110.00
null
0.00


-- *** FAIL *** This insert puts the account in violation
INSERT Operation (OperationId, AccountNo, Change)
VALUES
    (10, 200, -1)

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O ORDER BY O.AccountNo, O.OperationId
Msg 50000 Level 16 State 1 Line 34
Operation series failed validation for account 200 in TR_Operation_ValidateOperationSeries.

-- *** FAIL *** This change would make the "to account" 100 go negative
UPDATE Operation
SET AccountNo = 100 -- Reassign Change=-110 from AccountNo 200 -> 100
WHERE OperationId = 9

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId
Msg 50000 Level 16 State 1 Line 34
Operation series failed validation for account 100 in TR_Operation_ValidateOperationSeries.

-- *** FAIL *** This change would make the "from account" 200 go negative
UPDATE Operation
SET AccountNo = 100 -- Reassign Change=44 from AccountNo 200 -> 100
WHERE OperationId = 8

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId
Msg 50000 Level 16 State 1 Line 34
Operation series failed validation for account 200 in TR_Operation_ValidateOperationSeries.

-- Success - Both the from and to accounts remain positive
UPDATE Operation
SET AccountNo = 200 -- Reassign Change = 1 from AccountNo 100 -> 200
WHERE OperationId = 3

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId


OperationId
AccountNo
Change
SomeOtherValue
CummulativeAmount


1
100
1.00
null
1.00

2
100
2.00
null
3.00

4
100
-3.00
null
0.00

3
200
1.00
null
1.00

5
200
11.00
null
12.00

6
200
22.00
null
34.00

7
200
33.00
null
67.00

8
200
44.00
null
111.00

9
200
-110.00
null
1.00


-- *** FAIL *** This change would make the account 200 go negative
DELETE Operation
WHERE OperationId = 8 -- Deleting Change=44 from Account 200

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId
Msg 50000 Level 16 State 1 Line 34
Operation series failed validation for account 200 in TR_Operation_ValidateOperationSeries.

-- Success - Account 200 remains positive
DELETE Operation
WHERE OperationId = 9 -- Deleting Change=-110 from Account 200

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId


OperationId
AccountNo
Change
SomeOtherValue
CummulativeAmount


1
100
1.00
null
1.00

2
100
2.00
null
3.00

4
100
-3.00
null
0.00

3
200
1.00
null
1.00

5
200
11.00
null
12.00

6
200
22.00
null
34.00

7
200
33.00
null
67.00

8
200
44.00
null
111.00


-- *** FAIL *** Reordering the operations would make the sum go negative briefly
UPDATE Operation
SET OperationId = 12 -- Moving CHnage=3 record after some negative activity
WHERE OperationId = 2

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId
Msg 50000 Level 16 State 1 Line 34
Operation series failed validation for account 100 in TR_Operation_ValidateOperationSeries.

-- Success - Changing some other value does not trigger revalidation
UPDATE Operation
SET SomeOtherValue = 'I am the 1'
WHERE OperationId = 1

SELECT
    *,
    SUM(O.Change) OVER(PARTITION BY AccountNo ORDER BY O.OperationId) AS CummulativeAmount
FROM Operation O
ORDER BY O.AccountNo, O.OperationId


OperationId
AccountNo
Change
SomeOtherValue
CummulativeAmount


1
100
1.00
I am the 1
1.00

2
100
2.00
null
3.00

4
100
-3.00
null
0.00

3
200
1.00
null
1.00

5
200
11.00
null
12.00

6
200
22.00
null
34.00

7
200
33.00
null
67.00

8
200
44.00
null
111.00
OperationId	AccountNo	Change	SomeOtherValue	CummulativeAmount
1	100	1.00	null	1.00
2	100	2.00	null	3.00
3	100	3.00	null	6.00
4	100	4.00	null	10.00
5	200	11.00	null	11.00
6	200	22.00	null	33.00
7	200	33.00	null	66.00
8	200	44.00	null	110.00
OperationId	AccountNo	Change	SomeOtherValue	CummulativeAmount
1	100	1.00	I am the 1	1.00
2	100	2.00	null	3.00
4	100	-3.00	null	0.00
3	200	1.00	null	1.00
5	200	11.00	null	12.00
6	200	22.00	null	34.00
7	200	33.00	null	67.00
8	200	44.00	null	111.00