[Solved] MySql Trigger Function about status

by


If you are looking for an update statement that you would run periodically, and that sets the status of newly expired items, that would be:

update orders
set status="expired"
where curent_date > expired_date and status="new"

On the other hand… In your schema, status is a derived information, that is a column whose value can be inferred from the values of other columns. I would not recommend actually storing this information, because it would require an additional and tedious maintenance process.

Instead, you can create a view that computes the information on the fly when queried, and gives you an always up-to-date perspective at your data.

create view view_orders as
select
    order_date,
    expired_date,
    case when curent_date > expired_date then 'expired' else 'new' end status
from orders

1

solved MySql Trigger Function about status