All we need is an easy explanation of the problem, so here it is.
Here is my table:
create table passwordResetLink( link_id number(5) primary key, link_date timestamp(0) default sysdate NOT NULL, isExpired number(2) email varchar2(110) UNIQUE NOT NULL, linkUsed number(2) );
- How can I auto update isExpired column? I mean when record is inserted its value should auto set to
0and when 24 Hours passed the
0should be updated as
link_dateis timestamp of inserted record. The record that has passed one week should auto be deleted from the table
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
For the "isexpired" column, don’t store it as a literal column: use a view to determine that status dynamically. That way status is accurate to the second, at runtime.
create table passwordResetLink( link_id number(5) primary key, link_date timestamp(0) default sysdate NOT NULL, email varchar2(110) UNIQUE NOT NULL, linkUsed number(2) ); create or replace view passwordresetlink_view as select link_id, link_date, case when systimestamp > (link_date + interval '1' day) then 1 else 0 end isexpired, email, linkused from passwordresetlink;
For deletion of older records, use a scheduled job to run something like this:
delete from passwordresetlink where systimestamp > (link_date + interval '7' day);
Rather than query a table, why not query a view that has your rules built in?
CREATE VIEW vwPasswordResetLink AS SELECT link_id, link_date, CASE WHEN sysdate > link_date + INTERVAL '1' DAY THEN 1 ELSE 0 END AS isExpired, email, linkUsed FROM PasswordResetLink WHERE link_date >= sysdate - INTERVAL '7' DAY;
Then you can write a scheduled job to delete records older than 7 days to clear out expired records.
Alternatively, you could just use the above query to filter results and skip the creation of the view altogether …
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂