Re: problem with a trigger

There are some issues to comment:
[Quoted] 1.-The error is in the closing parenthesis near 'sun'. It should not exist.
2.-In the second IF, there should be a conversion function TO_NUMBER, since the comparision is between char (TO_CHAR) and numbers (7 or 00). 3.-Comparisions made with objects affected by NLS_PARAMS are tricky. If you change NLS_LANG you could get the daynames in other languages, and the comparision (='sun') will fail. The same if you use numbers. In some territories the first day of the week is Monday (for example, Spain), in another ones it happens to be sunday (England), you should avoid this by indicating the params inside the function. 4.-No reservations on sundays. Better with a check constraint, but if you want a especific user message then you should use a trigger (BEFORE INSERT OR UPDATE... FOR EACH ROW).
> CREATE TRIGGER reservations_restriction
> BEFORE
> DELETE OR INSERT OR UPDATE
> ON reservations
>
> BEGIN
> IF TO_CHAR(SYSDATE, 'DY') = 'sun') <--This parenthesis WRONG
> THEN raise_application_error( -20501, ' blah blah blah');
> END IF;
>
> IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) < 7
> OR
> TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) >= 0 THEN
> raise_application_error( -20502, ' blahblahblah');
> END IF;
> END;
"BlackEagle" <slimane_at_tsk.be> wrote in message news:<%BvBb.69580$oC2.2788492_at_phobos.telenet-ops.be>...
> Hi everyone,
> I have a table reservations with user-id, date... in it.
> Now I found a trigger to prevent people make changes on table reservations
> on sunday and any day before 7 am and after 23 pm.
> it looks like this but I keep get an error like "trigger built with
> compilation failure"
> what can I do to resolve this?
>
> CREATE TRIGGER reservations_restriction
> BEFORE
> DELETE OR INSERT OR UPDATE
> ON reservations
>
> BEGIN
> IF TO_CHAR(SYSDATE, 'DY') = 'sun')
> THEN raise_application_error( -20501,
> ' blah blah blah');
> END IF;
>
> IF (TO_CHAR(SYSDATE, 'HH24') < 7 OR
> TO_CHAR(SYSDATE, 'HH24') >= 00)
> THEN raise_application_error( -20502,
> ' blahblahblah');
> END IF;
> END; /
>
> and another question, in table reservations, I want to prevent people make
> reservations for sundays. because the business is closed that day.
> what can I add in the trigger for that, or what kind of check constraint can
> I use instead.
>
> ps. don't know if it does a matter but I'm using oracle 9i on xp
> to connect to the db username/pwd _at_oradb
>
> thanx in advance...
Received on Wed Dec 10 2003 - 14:14:17 CET