MySql Trigger Function about status

Question

I would like to ask a solution for mysql database. since i made database(order) about 3 column named as order_date(datatype - date),expired_date(datatype - date) and status(varchar(10)).

as status value are only New and Expired.[as only 'New' input from user]

The main process I want is

• as expired date(calculated from order_date) should know that currentday is doing well
• if currentday is greater than expired date. database should be triggered and update status column 'New' into 'Expired'

as far I know to accomplish this I should use an SQL trigger. How can I implement a trigger for the above use case.

Answer 1

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