Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to usdate records after some time periode of its insretion in table. Expand / Collapse
Author
Message
Posted Saturday, September 29, 2012 4:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 21, 2014 11:55 PM
Points: 64, Visits: 205
hello friends
I am working on a application where we are sending OTP as password to users but this OTP is active only for 2 hours. Now I want do some thing my database that update the status of OTP after 2 hours automaticaly according to it creation time. can any one help me?.
Post #1366178
Posted Monday, October 1, 2012 2:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 3, 2014 9:25 AM
Points: 895, Visits: 2,432
The only way that I can think of is to have an SQL Agent Job running on a schedule that updates the record that have expired. However unles you are running this every minute there will be a latency based on the schedule where some rows will not be expired until for the duration of the latency, so on a 10 minute schedule then you will have rows that may not expire for <expire duration> + Schedule, depending on when they were due to expire.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1366388
Posted Monday, October 1, 2012 3:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Don't use "status." Use an expiry timestamp instead. Then use that when you query to see whether the entry has expired.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1366440
Posted Monday, October 1, 2012 3:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 1,796, Visits: 5,800
dwain.c (10/1/2012)
Don't use "status." Use an expiry timestamp instead. Then use that when you query to see whether the entry has expired.

+1


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1366447
    Posted Monday, October 1, 2012 4:19 AM
    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: Friday, October 3, 2014 9:25 AM
    Points: 895, Visits: 2,432
    nice once dwain, never thought of that, though I suppose it depends on how much you can change the underlying database structure.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices
    Post #1366463
    Posted Monday, October 1, 2012 4:37 AM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Tuesday, October 7, 2014 10:53 PM
    Points: 3,421, Visits: 5,359
    Jason-299789 (10/1/2012)
    nice once dwain, never thought of that, though I suppose it depends on how much you can change the underlying database structure.


    Actually I see this all the time. People create a field called status and then set it active/inactive. Without an "inactive" date, this is not a very extensible design because it means you can't mark the "thing" in the table as "to be deactivated on <some future date>."



    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
    Since random numbers are too important to be left to chance, let's generate some!
    Learn to understand recursive CTEs by example.
    Splitting strings based on patterns can be fast!
    Post #1366473
    Posted Monday, October 1, 2012 5:53 AM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Tuesday, October 7, 2014 10:53 PM
    Points: 3,421, Visits: 5,359
    If you want to get fancy-smancy, you could do it like this:

    CREATE TABLE #Users 
    (UserID VARCHAR(20) NOT NULL
    ,Created_DT DATETIME DEFAULT (GETDATE())
    ,Expiry_DT DATETIME DEFAULT (DATEADD(hour, 2, GETDATE()))
    ,Is_Expired AS CASE WHEN GETDATE() > Expiry_DT THEN 'Y' ELSE 'N' END)

    INSERT INTO #Users (UserID)
    SELECT 'Dwain'

    SELECT * FROM #Users

    -- When User validates
    UPDATE #Users SET Expiry_DT = '9999-12-31' WHERE UserID = 'Dwain'
    SELECT * FROM #Users

    DROP TABLE #Users


    Now you have an Is_Expired flag you can query.

    Edit: The only downside to this approach is you can't index on the computed Is_Expired column because it is not persisted, and you can't make it persisted (I believe) because it is non-deterministic.



    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
    Since random numbers are too important to be left to chance, let's generate some!
    Learn to understand recursive CTEs by example.
    Splitting strings based on patterns can be fast!
    Post #1366503
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse