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.
Advertisement
Answer
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