Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Unanswered: Trigger activates with change in date

    I want to know if it is possible to activate a trigger based on a change in the system date.
    The event is the change of date, rather than an update of a variable in a table.


    So basically I want to achieve something like

    Create or replace trigger after update OF sysdate

    Or something along those lines.

    Is there anyway possible????

    Thanks for any help

  2. #2
    Join Date
    Dec 2003
    Posts
    74
    but is the date not consistently changing away?

    are you referring to changing the date for a session

    or

    changing the date for an entiry database?


    regards

    edwin

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The Oracle DBMS_JOB package provides scheduling capabilities.
    Last edited by r123456; 12-30-03 at 11:04.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Dec 2003
    Posts
    74
    yes but if you schedule a job to see if the date has changed with trigger everytime as the date / time is always changing.

    - You should put system date into a table column each evening.

    - When you run the job next day check the sysdate against the fixed stored date and this will allow you to trigger your procedure.

    e.g. trunc(sysdate) != trunc(p_stored_date)

    -At the end of that day then put system date into a table column again


    Catch my drift?
    Last edited by edwinjames; 12-31-03 at 07:29.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    If the DBMS was not shut down the night before, there would be no method to determine the next execution time of the procedure. Of course the user can run a procedure manually that checks the current SYSDATE agains't the stored date, however the problem is still how the SYSDATE is inserted into the table.

    By implementing the DBMS_JOB package, each day at midnight the PL/SQL procedure can be executed to determine if a new month has commenced. If so then execute the relevant code. The interval between each execution of the procedure can be set to 24hrs.
    Last edited by r123456; 12-30-03 at 11:39.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Sep 2003
    Posts
    12
    Thanks everyone for the idea, the DBMS_JOB package notion does work but the assignment forbids using that functionality.

    Thanks 'edwinjames' I got your drift, and have implemented it successfully.

    Again thanks to everyone

Posting Permissions

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