Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2009
    Posts
    9

    Unanswered: update colum after given amount of time

    Hi,

    I am trying to write something that will update the time in a field after a given time. I am not sure if i should be creating a trigger or a query. any suggestions would be helpful.

    thanks

    gisler

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am trying to write something that will update the time in a field after a given time. I am not sure if i should be creating a trigger or a query. any suggestions would be helpful.

    I suggest using SQL to meet your requirements; what ever they may actually be.
    Please specify.

    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would you, perhaps, want to schedule a job that will do that task?

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool The purpose is...

    Quote Originally Posted by gisler View Post
    Hi,

    I am trying to write something that will update the time in a field after a given time. I am not sure if i should be creating a trigger or a query. any suggestions would be helpful.

    thanks

    gisler

    Is there a purpose to this requirement or is it just "for fun"?

    Sometimes the date/time columns are designed to be updated depending on some event that occurs and is related to the particular data in that row. In this case you may use triggers that fire when the dml event occurs and update these columns.

    As there may be many other reasons to update a date/time column, the solution may depend on the requirement to do so.

    HTH
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Dec 2009
    Posts
    9
    i want to use sql but i am not sure how to write the sql for it. i am ok with creating queries but thats about it. i just need to have a colum increment from 1-4 after a set amount of hours has passed. if you could point me in the right direction that would be good. the oracle version i am using is 10g.

    thanks
    gisler

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Use dbms_Job

    You could use the dbms_job pl/sql package to do it (as mentioned by littlefoot).
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why do you want a flag to increment to 4 from 1. Simply store the date and time in the row and you can find any record that is older then x number of hours. That is how you do it!
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Dec 2009
    Posts
    9
    yeah i think i do need to use a trigger. what i really meant to say in my first post was that after a given time i need a colum to update. i am having trouble writing the sql that will compare the time in my colum to the sysdate and then updating a field when a certain amount of time has passed. sorry if i am a bit clueless.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Sysdate

    Quote Originally Posted by gisler View Post
    yeah i think i do need to use a trigger. what i really meant to say in my first post was that after a given time i need a colum to update. i am having trouble writing the sql that will compare the time in my colum to the sysdate and then updating a field when a certain amount of time has passed. sorry if i am a bit clueless.
    To compare time try:
    Code:
    SELECT SYSDATE - time_in_table as days_elapsed FROM My Table;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Dec 2009
    Posts
    9
    ok thanks for that

    this is what i have so far. it does not like the 4th line.

    Code:
    select problem_severity, call_date,  sysdate - call_date 
    as timepassed 
    from call
    where timepassed > 2 
    update problem_severity
    set problem_severity = 2

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    That's invalid SQL. The following is my re-interpretation

    Code:
    update call
    set problem_severity = 2
    where sysdate - call_date > 2;
    Know that (sysdate - call_date > 2) is in days, so when the call date is more than 2 days old, you'd be updating PROBLEM_SEVERITY to 2. You maybe want something more like:

    Code:
    update call
    set problem_severity = 2
    where problem_severity !=2  and sysdate - call_date > 2;
    So you don't keep updating the same records back to a PROBLEM_SEVERITY of 2.

    --=cf

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The main point here should be is that a trigger is not going to go to sleep for three hours. A trigger is fired when some type of DML activy occurs(insert/update/delete), not time.You need to schedule your app/job scheduler/cron/whatever to go out and check every hour or whatever frequency for what problems need to be escalated.

    Dave

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select decode(greatest,(sysdate - call_date > 2 ),2),2,4,2) problem_severity
    from my_table;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  14. #14
    Join Date
    Dec 2009
    Posts
    9
    Hi,

    thanks for all your help. i am giving up on it. it was just an excercise for me. think i might try something more simpler. thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •