I have created a mysql table with the following columns...
item_price, discount, delivery_charge, grand_total
The value of item_price, discount, and delivery_charge all vary depending on the item under consideration, and these would be filled into the table manually.
I would like to have mysql populate the value in the grand_total column based on the values of item_price, discount, and delivery_charge, using the formula below...
grand_total = item_price - discount + delivery_charge
Can I somehow specify a formula for this column while creating the table structure in mysql? I know this can be done using php, but I prefer the database doing this for me automatically, if possible.
Here's an example of a trigger that will work. Note that you'll need both update and insert triggers due to your "ad hoc update" requirements.
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 ;
But wouldn't a view be simpler and better?
create view my_table_view as
select item_price, discount, delivery_charge,
item_price - discount + delivery_charge as grand_total
from my_table;
then just select from the view instead of the table
I think you'd have to use a BEFORE INSERT trigger for that:
http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html
Something like this (untested off the top of my header code):
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;
