Re: problem with a trigger

From: Frank <fbortel_at_nescape.net>
Date: Thu, 18 Dec 2003 20:29:49 +0100
Message-ID: <brsumd$6ff$1_at_news1.tilbu1.nb.home.nl>

Aaron V wrote:

> Forgive me if I'm missing the point here, I've not been following this
> thread closely, but if you're trying to see if a date is Sunday,(not which
> day of the week Sunday is in your part of the globe), why not just compare
> that.
>
> If To_Char(res_date, 'DY') = 'SUN' Then ....
>
> --Aaron V
>
> "BlackEagle" <slimane_at_tsk.be> wrote in message
> news:ZmkCb.74190$Pr4.2955402_at_phobos.telenet-ops.be...
>

>>"Mark C. Stock" <mcstockX_at_Xenquery .com> schreef in bericht
>>news:o_idnVNp_rmkVkSiRVn-sQ_at_comcast.com...
>>
>>>|
>>>| Ok Guido,
>>>| I know that you've pointed me to the errors.
>>>| I had read it but I couldn't figure out what you meant.
>>>| My other problem is this trigger.
>>>|
>>>| insert into reservaties values (101,'14-DEC-2003');
>>>|                                                (userid,res_date)
>>>| 14 december 2003 is a sunday.
>>>| so by inserting this record, the trigger must do his job by not

>
> allowing
>

>>>| this insertion.
>>>|
>>>| CREATE TRIGGER res_sunday
>>>| BEFORE INSERT ON reservations
>>>| DECLARE
>>>| res_date date;
>>>|    BEGIN
>>>|    IF TO_NUMBER(TO_CHAR(to_date(res_date,'dd-mon-yyyy'),'d')) = 7
>>>|      THEN raise_application_error(-20000,'We are closed on sunday');
>>>|    END IF;
>>>|    END;
>>>| /
>>>|
>>>| Thanx in advance
>>>|
>>>|
>>>
>>>i've not been following this thread, but a couple things caught my eye

>
> on
>

>>>the above code samples -- hope i'm not being redundant or missing
>>
>>something
>>
>>>discussed earlier
>>>
>>>
>>>it might be just a big typo trying to illustrate the point, but the
>>
>>correct
>>
>>>syntax for the insert should be
>>>
>>>insert into reservaties (userid,res_date) values
>>>(101,to_date('14-DEC-2003','dd-MON-yyyy');
>>>
>>>also note the explicit date conversion  when using a date literal
>>>
>>>
>>>if the point is to not allow the RES_DATE column to include Sundays,

>
> then
>

>>>you could use a check constraint
>>>
>>>alter table reservaties add constraint res_date_chk check
>>>(to_char(res_date,'d') <> '1');
>>>
>>>since the value of to_char(..., 'd') depends on NLS_TERRITORY, to be

>
> safe
>

>>>you could compare the date to a known sunday:
>>>
>>>alter table  reservaties  add constraint res_date_chk check (
>>>    to_char(res_date,'d') <>
>>>    to_char(to_date('12072003','mmddyyyy'),'d')
>>>)
>>>
>>>the trigger equivalent of this is:
>>>
>>>CREATE OR REPLACE TRIGGER res_sunday
>>>   BEFORE INSERT OR UPDATE
>>>   ON RESERVATIES
>>>   FOR EACH ROW
>>>DECLARE
>>>BEGIN
>>>   IF TO_CHAR(:NEW.res_date, 'd') =
>>>                                  TO_CHAR(TO_DATE('12072003',

>
> 'mmddyyyy'),
>

>>>'d')
>>>   THEN
>>>      raise_application_error(-20000, 'We are closed on sunday');
>>>   END IF;
>>>END;
>>>
>>>note the use of 'OR REPLACE', 'OR UPDATE', and 'FOR EACH ROW', and the
>>>reference to the :NEW.res_date value
>>>
>>>
>>>however, if the point is to not allow inserts (and presumably updates

>
> and
>

>>>deletes) to occur on sundays, then the trigger would check SYSDATE

>
> instead
>

>>>of :NEW.res_date,
>>>and would not include the FOR EACH ROW (can't use a constraint, because
>>>SYSDATE can't be referenced in constraints)
>>>
>>>CREATE OR REPLACE TRIGGER res_sunday
>>>   BEFORE INSERT OR UPDATE OR DELETE
>>>   ON RESERVATIES
>>>DECLARE
>>>BEGIN
>>>   IF TO_CHAR(SYSDATE, 'd') =
>>>                                  TO_CHAR(TO_DATE('12072003',

>
> 'mmddyyyy'),
>

>>>'d')
>>>   THEN
>>>      raise_application_error(-20000, 'We are closed on sunday');
>>>   END IF;
>>>END;
>>>
>>>
>>>-- mcs
>>>
>>>
>>>
>>
>>thanx a lot...
>>it's working now :)
>>thanx for all the help and support.
>>
>>friendly greetz
>>
>>

>
>
>

The sheer simplicity of it - beautiful.
What if TO_CHAR(sysdate,'DY') delivers 'ZON'? Same dependencies... (now on NLS_LANG)

-- 
Merry Christmas and a Happy New Year,
Frank van Bortel

Received on Thu Dec 18 2003 - 20:29:49 CET