auto set value of a specific column after 24 hours and to delete record after one week: Oracle SQL

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)
);
  1. How can I auto update isExpired column? I mean when record is inserted its value should auto set to 0 and when 24 Hours passed the 0 should be updated as 1
  2. link_date is timestamp of inserted record. The record that has passed one week should auto be deleted from the table

Thanks

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.

Method 1

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);

Method 2

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply