SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE FUNCTION ValidateOperationSeries (
    @AccountNo INT,
    @Change NUMERIC(19,2) -- Ignored, but must be present to catch column updates
) 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),
    CONSTRAINT CK_Operation_ValidateOperationSeries
        CHECK (dbo.ValidateOperationSeries(AccountNo, Change) = 1)
)
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
CummulativeAmount


1
100
1.00
1.00

2
100
2.00
3.00

3
100
3.00
6.00

4
100
4.00
10.00

5
200
11.00
11.00

6
200
22.00
33.00

7
200
33.00
66.00

8
200
44.00
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
CummulativeAmount


1
100
1.00
1.00

2
100
2.00
3.00

3
100
3.00
6.00

4
100
-5.00
1.00

5
200
11.00
11.00

6
200
22.00
33.00

7
200
33.00
66.00

8
200
44.00
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 547 Level 16 State 0 Line 3
The UPDATE statement conflicted with the CHECK constraint "CK_Operation_ValidateOperationSeries". The conflict occurred in database "fiddle_51e82f96a48e42c38b0714f3069004a2", table "dbo.Operation".
Msg 3621 Level 0 State 0 Line 3
The statement has been terminated.

-- 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
CummulativeAmount


1
100
1.00
1.00

2
100
2.00
3.00

3
100
3.00
6.00

4
100
-3.00
3.00

5
200
11.00
11.00

6
200
22.00
33.00

7
200
33.00
66.00

8
200
44.00
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
CummulativeAmount


1
100
1.00
1.00

2
100
2.00
3.00

3
100
1.00
4.00

4
100
-3.00
1.00

5
200
11.00
11.00

6
200
22.00
33.00

7
200
33.00
66.00

8
200
44.00
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
CummulativeAmount


1
100
1.00
1.00

2
100
2.00
3.00

3
100
1.00
4.00

4
100
-3.00
1.00

5
200
11.00
11.00

6
200
22.00
33.00

7
200
33.00
66.00

8
200
44.00
110.00

9
200
-110.00
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 547 Level 16 State 0 Line 2
The INSERT statement conflicted with the CHECK constraint "CK_Operation_ValidateOperationSeries". The conflict occurred in database "fiddle_51e82f96a48e42c38b0714f3069004a2", table "dbo.Operation".
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.
OperationId	AccountNo	Change	CummulativeAmount
1	100	1.00	1.00
2	100	2.00	3.00
3	100	3.00	6.00
4	100	4.00	10.00
5	200	11.00	11.00
6	200	22.00	33.00
7	200	33.00	66.00
8	200	44.00	110.00