Updating the status after 30 min automatically by using database calls

Question

Updating the status after 30 min automatically by using database calls

• I have Master table with two coloumns (ID , Active) values like (val1 , 0(Active)) (1 not active)

• Another table Transaction with three fields (ID , Locked Time , Unlocked Time) values like (val1,11:00 AM,11:30 AM).

• If the user wrongly enter password or user name three times user activation status is updated to not active.that time is stored in Locked time.

• I want after 30 min the user will be unlocked and the status will change to active(0).

• We have bulk users like that for monthly 400 users deactivated every time i am unlocking manually.

• I want solution as database wise not application...like after 30 min one trigger will update that or any stored procedure queries.

Please heip me...thank you in advance.

Answer 1

You should have some cron job scheduled which will run for some threshold time like 5 min or 10 min etc which will check for the locked time and compare with system time if it is more than half an hour then it will updated as active.

~Temruzinn

Answer 2

If you want a solution in database, then it would be database-specific. If you using oracle, you could have a DBMS_JOBS or dbms_scheduler job running every minute and a procedure, which it will execute, to check the database for such deactivated accounts and act accordingly.

Procedure could say something like this in PL/SQL (assuming you stored the locked and unlocked times in date format):

BEGIN
  update transaction_tab
   set status = 'Active', unlocked_time = sysdate
  where status = 'Inactive'
   and sysdate-locked_time >= 1 / (24 * 2); // 30 mins
END;