Geek asks and answers

How to populate a mysql column value based on a formula?

I created a mysql table with the following columns ...

item_price, discount, delivery_charge, grand_total

The value item_price, discountand delivery_chargeeverything varies depending on the item in question, and they will be filled in manually.

I would like mysql to populate a value in a column grand_totalbased on values item_price, discountand delivery_chargeusing the following formula ...

grand_total = item_price - discount + delivery_charge

Is there any way to specify the formula for this column when creating the table structure in mysql? I know that this can be done with php, but I prefer the database to do this automatically for me, if possible.

+3
source share
2 answers

, . , , - "ad hoc update".

delimiter ~

create trigger my_table_update before update on my_table
for each row begin
    set new.grand_total = new.item_price - new.discount + new.delivery_charge;
end~

create trigger my_table_insert before insert on my_table
for each row begin
    set new.grand_total = new.item_price - new.discount + new.delivery_charge;
end~

delimiter ;

?

create view my_table_view as
select item_price, discount, delivery_charge, 
item_price - discount + delivery_charge as grand_total
from my_table;

+7

, BEFORE INSERT :

http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html

- ( ):

CREATE TRIGGER blahblah BEFORE INSERT ON your_table
FOR EACH ROW BEGIN
    set new.grand_total = new.item_price - new.discount + new.delivery_charge;
END;
+1

More articles:


All Articles