Update Balance column automatically in a balance ledger for different Customer IDs

Question

I am using MariaDB Version 10.2.22

I have a ledger table which can be defined by the sql code below:

CREATE TABLE ledger (
  tr_id    int AUTO_INCREMENT NOT NULL COMMENT 'Transaction ID ( Primary Key)',
  cust_id  int NOT NULL COMMENT 'Customer ID',
  `date`   date NOT NULL COMMENT 'Transaction date',
  credit   decimal(10,2) DEFAULT NULL COMMENT 'Credit - paid in',
  debit    decimal(10,2) DEFAULT NULL COMMENT 'Debit - Money paid out',
  balance  decimal(10,2) DEFAULT NULL COMMENT 'Bank Balance = Credt-debit + bank Balance of previous record of the same customer ID',
  /* Keys */
  PRIMARY KEY (tr_id)
) ENGINE = InnoDB;

I have filled the table manually and entered the balance by hand.

I tr_id     I   cust_id  I date                I  credit        I  debit         I   balance     I
-------------------------------------------------------------------------------------------
I  1          I    100      I  2020-07-01   I 1000.00     I                   I   1000.00     I
I  2          I    500      I  2020-07-02   I 2000.00     I                   I    2000.00    I
I  3          I    100      I  2020-07-03   I                   I   200.00     I      800.00    I
I  4          I    500      I  2020-07-04   I                   I   500.00     I    1500.00    I

The balance for each record = (credit -debit) + last Balance of the same cust_id

I want to update the balance column automatically on inserting values in the cust_id, date, credit, or debit field. Is it possible to define a trigger that would automatically update the balance field when the credit field or debit field have altered values? I would like to get the SQL code for such a function. Thanking you in anticipation

Answer 1

I would not recommend storing the balance. This is a derived information, that can easily be computed on the fly when needed, typically using a window sum() (available in MySQL 8.0 or MariaDB 10.3 and higher).

You can, instead, create a view: this gives you an always up-to-date perspective at your data, that you can query just like you would query a regular table.

Just remove the balance column from the ledger table, and create the view as follows:

create view v_ledger as 
select 
    l.*,
    sum(coalesce(credit, 0) - coalesce(debit, 0)) 
        over(partition by cust_id order by date, tr_id) balance
from ledger l