I'm Working on A c# form Application with mysql Database.

below Is design of ,table i'm using to save my data for customer with following columns..

id : auto-increment id for identify each row and getting balance for customer with max id of cid

cid : customer's ID

billno : invoice number deposit : the amount being deposit against that bill billtotal : the total amount of bill...

balance : previous balance + current billtotal - current billdeposit

I'm calculating balance with my form application and everything works fine

Now the problem is with deleting a bill or updating a bill

suppose in bill no. 9 i have entered 100 instead of 1000 Now i wants to change that value

if I'll change that then all my calculations of balance column from that row to last row will be wrong.

what will best way to Do So....

i'm thinking of a trigger after update for this but i don't know how to create a trigger to perform these calculation only for current row to last row and only for that customers cid

I would not cache the balance.

SELECT SUM(billtotal)-SUM(deposit) FROM table WHERE cid = x

This is a pretty simple query, which should operate fine on every database even with lots of rows. Put an index on the cid column and you are good to go without saving the balance and the problems which arise out of this caching column.