Re: problem with a trigger

[Quoted] [Quoted] 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
>
>
Received on Thu Dec 18 2003 - 05:45:02 CET