Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    18

    Unanswered: SYSDATE trigger problem

    Hia,

    I want the following trigger to flag up an error when the insert has a startdate equal to the system date:

    create or replace trigger tour_tg
    after insert on tour
    for each row
    begin
    if :new.startdate=sysdate then
    RAISE_APPLICATION_ERROR (
    -20100,'Date should be after today's date');
    END IF;
    END;


    However the above doesnt seem to work as it lets me insert a row with todays date

    I also tried:

    create or replace trigger tour_tg
    after insert on tour
    declare
    tdate date;
    for each row
    begin
    select distinct sysdate into tdate from tour;
    if :new.startdate=tdate then
    DBMS_OUTPUT.PUT_LINE ('Current date is '|| sysdate);
    END IF;
    END;


    but I get a compilation error regarding the 'for each row' command. Why is this ?

    Thanks in adv

    Greg

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    maybe your logic is wrong, try compare if startdate is equal or less then sysdate
    Code:
    create or replace trigger tour_tg
    after insert on tour
    for each row
    begin
    if :new.startdate<=sysdate then
    RAISE_APPLICATION_ERROR (
    -20100,'Date should be after today's date');
    END IF;
    END;
    or if your start date is in Day-Month-Year format (you don't use time) use to_date(sysdate)
    Code:
    create or replace trigger tour_tg
    after insert on tour
    for each row
    begin
    if to_date(:new.startdate)<=to_date(sysdate) then
    RAISE_APPLICATION_ERROR (
    -20100,'Date should be after today's date');
    END IF;
    END;

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

    Cool


    Or,...You may want to use a before trigger instead.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A date always contains a time function so your dates will almost never equal, but you can use a trunc to strip off the time portion and do what you want. Also use a before trigger, so the insert is never done. Also you are not checking to see if the start date is in the past.

    create or replace trigger tour_tg
    before insert on tour
    for each row
    begin
    if trunc(:new.startdate) <= trunc(sysdate) then
    RAISE_APPLICATION_ERROR (
    -20100,'Date should be after today's date');
    END IF;
    END;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by madafaka
    ... use to_date(sysdate)
    I think you meant TRUNC(sysdate)?

  6. #6
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Quote Originally Posted by WilliamR
    I think you meant TRUNC(sysdate)?
    actually I didn't to_date() gives you the same result as trunc()

    so if you have
    date1 := '01-Oct-05 11:21:30 AM'
    date2 := '01-Oct-05 03:56:47 PM'

    date1 <> date2
    but
    to_date(date1) = to_date(date2)
    as well as
    trunc(date1) = trunc(date2)

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by madafaka
    actually I didn't to_date() gives you the same result as trunc()

    so if you have
    date1 := '01-Oct-05 11:21:30 AM'
    date2 := '01-Oct-05 03:56:47 PM'

    date1 <> date2
    but
    to_date(date1) = to_date(date2)
    as well as
    trunc(date1) = trunc(date2)
    To_Date does NOT give you the same result as trunc. When you are using to_date, you are performing an implicit conversion of sysdate (or any date variable) to character and then back to date. If your default date format only contain date information and NO time element, then it will work. If it contains a time format, then it will NOT. For example.

    1* select to_char(to_date(sysdate),'MM/DD/YYYY hh24:mi') from dual
    SQL> /

    TO_CHAR(TO_DATE(
    ---------------
    11/04/2005 00:00

    1* alter session set nlS_date_format='MM/DD/YYYY HH24:MI'
    SQL> /

    session altered.

    SQL> select to_char(to_date(sysdate),'MM/DD/YYYY hh24:mi') from dual;

    TO_CHAR(TO_DATE(
    ----------------
    11/04/2005 08:39
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I was talking abou comparison:

    if you have
    date1 := '01-Oct-05 11:21:30 AM'
    date2 := '01-Oct-05 03:56:47 PM'

    then
    trunc(date1) = trunc(date2) is true
    to_date(date1) = to_date(date2) is true as well

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It still makes no sense. Why have the overhead of "date -> text -> date" conversion for both columns when trunc will simply mask the last three bytes of the date column. But I guess that's the beauty of Oracle, there are always multiple ways to get to the final result.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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