Re: problem with a trigger

[Quoted] "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 Fri Dec 12 2003 - 15:35:37 CET