CODEWITHSUNDEEP
mysql
Avaronald
Avaronald

Reputation: 25

MySQL on update set value of one column as sum of two columns

I'm developing a pos application and I thought that I could reduce the amount of code in the program and prevent errors by letting the mysql server do some work for me. What I would like to is MySQL to calculate the value of one column based on the other value added to table.

My MySQL table looks like this:

| id | tax_group | amount | balance | date |

As I understand it, you can add triggers to the table that when a new amount value is added, it would automatically count the balance (ie. balance += amount). This would also prevent the user manipulate the taxes. However, I don't know how to do this. I've looked the MySQL documentation, but it didn't help.

I'd appreciate greatly if you could help me.

Upvotes: 0

Views: 1649

Answers (3)

Hitesh Mundra
Hitesh Mundra

Reputation: 1588

I think you don't need for trigger. when you update amount then you can set balance to old balance + new_amount 

update tax_sales set amount=20, balance=balance+amount where id=1;

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You will need to have 2 triggers to accomplish this. The first one when you add a new record and the 2nd one while you update a record. The triggers could be as

delimiter //

create trigger before_ins_cal_balance before insert on your_table_name
for each row
begin
 set new.balance = new.amount ;
end;//

delimiter ;



delimiter //

create trigger before_upd_cal_balance before update on your_table_name
for each row
begin
 if new.amount <> old.amount then
    set new.balance = old.balance+new.amount ;
 end if;
end;//

delimiter ;

Here the first one will set the same value as amount to balance, and the 2nd one while updating will set the new balance as previous balance + old amount. Note that the balance amount will be updated in the 2nd case if only the amount is changed, else it will have the previous value

Upvotes: 0

Ingo Plate
Ingo Plate

Reputation: 31

this could help you, but I think the math-part is not really what you want, but you'll be able to modify it!

create trigger insert_amount 
before insert on my_table for each row
  set NEW.balance  = NEW.balance + NEW.amount;


create trigger update_amount 
before update on my_table for each row
  set NEW.balance  = NEW.balance + NEW.amount;

Upvotes: 0

Related Questions