Re: problem with a trigger
From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 18 Dec 2003 12:30:15 -0800
Message-ID: <1071779320.592407_at_yasure>
>> 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
>>>
>>>
>>
>>
>>
Date: Thu, 18 Dec 2003 12:30:15 -0800
Message-ID: <1071779320.592407_at_yasure>
Frank wrote:
> Aaron V wrote: >
>> 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
>>>
>>>
>>
>>
>>
> [Quoted] > The sheer simplicity of it - beautiful. > What if TO_CHAR(sysdate,'DY') delivers 'ZON'? > Same dependencies... (now on NLS_LANG)
I think that is why they invented UTL_GDK and other tools.
-- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan_at_x.washington.edu (replace 'x' with a 'u' to reply)Received on Thu Dec 18 2003 - 21:30:15 CET