Perform Calculation after table has updated-CodePudding

I am working on a phpmysql project I have given myself in a bid to learn more about queries. I have added a new column called CALCULATION.

What I want to do is once the database is updated, a calculation is performed. That calculation would be:

VOLUME * (HIGH - LOW)

and that value will appear in the corresponding row under the CALCULATION column.

So, taking AAL for example, from the screenshot posted:

24847975 * (20.02 - 19.36) = 16399663.50 (would appear under CALCULATION column for AAL.

So the table is updated daily and then when it gets updated I'd like to perform the calculation on it. I'm looking at the idea that it may be TRIGGERS but would appreciate some guidance on this.

Would be useful to only perform this calculation if there is nothing inside the row as it would be a waste of resources recalculating everything all over again.

CodePudding user response：

I would recommend creating a view that has a calculation column that is aggregate of HIGH * LOW. Something like following:

CREATE VIEW [viewName] AS
SELECT (high*low) as calculation
FROM tableName

CodePudding user response：

Triggers are good for your need! They permit to perform operation after an operation on a single row. You can define on your table a trigger for INSERT and a trigger for UPDATE (they could be performed before or after the operation)

When you perform an INSERT (or UPDATE) the trigger can calculate the valute and it into your colum.

Another solution more simple could be to perform an other update query after you have finish the table update.

CodePudding user response：

Have a generated column:

create table ...
(
    ...
    calculation float GENERATED ALWAYS AS (VOLUME * (HIGH - LOW)),
    ...
)

(You can chose another data type if you want, I don't know MySQL very well...)