Re: problem with a trigger
Date: Fri, 12 Dec 2003 09:04:53 -0500
Message-ID: <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 [Quoted] the above code samples -- hope i'm not being redundant or missing something [Quoted] discussed earlier
it might be just a big typo trying to illustrate the point, but the correct [Quoted] 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 [Quoted] 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 [Quoted] 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
[Quoted] deletes) to occur on sundays, then the trigger would check SYSDATE instead
of :NEW.res_date,
[Quoted] 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