Re: problem with a trigger

From: Mark C. Stock <mcstockX_at_Xenquery>
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
Received on Fri Dec 12 2003 - 15:04:53 CET

Original text of this message