By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE balance (
id INT AUTO_INCREMENT PRIMARY KEY,
person_id INT NOT NULL,
action DECIMAL(10, 2) NOT NULL,
balance DECIMAL(10, 2) NOT NULL DEFAULT 0,
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
CONSTRAINT balance_cannot_be_negative CHECK (balance >= 0)
);
CREATE TRIGGER set_balance
BEFORE INSERT ON balance
FOR EACH ROW
SET NEW.balance = COALESCE(
(
SELECT balance
FROM balance
WHERE person_id = NEW.person_id
ORDER BY created_at DESC LIMIT 1
), 0) + NEW.action;
INSERT INTO balance (person_id, action) VALUES (1, 100); -- add $100, balance $100
INSERT INTO balance (person_id, action) VALUES (1, -20); -- spend $20, balance $80
INSERT INTO balance (person_id, action) VALUES (2, 50); -- add $50, balance $50
INSERT INTO balance (person_id, action) VALUES (1, 10); -- add $10, balance $90
INSERT INTO balance (person_id, action) VALUES (2, 30); -- add $30, balance $80
INSERT INTO balance (person_id, action) VALUES (2, -40); -- spend $40, balance $40
-- try to spend $100 which is over current balance, error generated
INSERT INTO balance (person_id, action) VALUES (2, -100);
Check constraint 'balance_cannot_be_negative' is violated.
SELECT * FROM balance ORDER BY id;
SELECT * FROM balance ORDER BY person_id, created_at;
| id | person_id | action | balance | created_at |
|---|---|---|---|---|
| 1 | 1 | 100.00 | 100.00 | 2023-06-08 20:05:05.998410 |
| 2 | 1 | -20.00 | 80.00 | 2023-06-08 20:05:06.003067 |
| 3 | 2 | 50.00 | 50.00 | 2023-06-08 20:05:06.004083 |
| 4 | 1 | 10.00 | 90.00 | 2023-06-08 20:05:06.004809 |
| 5 | 2 | 30.00 | 80.00 | 2023-06-08 20:05:06.005767 |
| 6 | 2 | -40.00 | 40.00 | 2023-06-08 20:05:06.006550 |
| id | person_id | action | balance | created_at |
|---|---|---|---|---|
| 1 | 1 | 100.00 | 100.00 | 2023-06-08 20:05:05.998410 |
| 2 | 1 | -20.00 | 80.00 | 2023-06-08 20:05:06.003067 |
| 4 | 1 | 10.00 | 90.00 | 2023-06-08 20:05:06.004809 |
| 3 | 2 | 50.00 | 50.00 | 2023-06-08 20:05:06.004083 |
| 5 | 2 | 30.00 | 80.00 | 2023-06-08 20:05:06.005767 |
| 6 | 2 | -40.00 | 40.00 | 2023-06-08 20:05:06.006550 |