How to subtract inserted value from previous rows field value for each entry in a statement

I need to do an insert into a table of each shipment that day. I am having a problem deducting the total amount from the previous row. I am inserting into tableC, but since if I have multiple orders in a day its only grabbing the last debit entry and deducting from the original last rows total. I know this is because I am using the ORDER BY DESC LIMIT 1, but I can't think of another way to get the last rows total column value. I also need the second insertion to get the new total from the previous insertion.

I am using a select statement inside a variable to run inside my insert statment. @vTotal

If its a single entry insertion it would work, but how do I this to work if there is multiple rows in the select statement, or is there a better way to do this?

Below is my current test query:

SET @vCreatedbt := 'name@domain.com';
SET @vTotal := (SELECT tableC.total - tableB.ShipmentCharge AS total 
                FROM tableC, tableB 
                ORDER BY id DESC 
                LIMIT 1);
SET @vwork_order_id := null;
SET @vModified := null;
SET @vModified_by := null;
SET @vCredit := null;
SET @vid := null;

INSERT INTO tableC
    SELECT @vId AS id, client_name AS descr, @vWork_order_id AS work_order_id, 
            @vCredit AS credit, ShipmentCharge as debit, @vTotal AS total, 
            now() as created, @vCreatedby as createdby, 
            @vModified AS modified, @vModified_by AS modified_by
    FROM tblA
        INNER JOIN tableD ON tableA.id = tableD.id
        INNER JOIN tableB ON tableD.tracking_number = tableB.TrackingNumber
        WHERE tableA.status = 'Order Shipped'

Just showing these three fields of tableC to simplify

Query is giving me

The id is auto incremented

DEMO:

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); 

> error
> Check constraint 'balance_cannot_be_negative' is violated.
>

SELECT * FROM balance ORDER BY id;
SELECT * FROM balance ORDER BY person_id, created_at;

fiddle

DATETIME(6) needed in the fiddle - common DATETIME does not provide needed accuracy. In practice DATETIME must be enough - I doubt that the person can perform two separate transactoins within a second.. or you may use not created_at but id for rows ordering.