Re: Checking for Overlaping Date Range

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1998/01/19
Message-ID: <69v4ov$3am$1@news01.btx.dtag.de>#1/1

Hi,

maybe this gives you an idea.

create or replace trigger test
before insert of key, von_datum, end_date on check_datum
for each row
declare

   dummy integer;
begin

   if :new.begin_date > :new.end_date then

      raise_application_error(-20500,'End-Date > Begin-Date is wrong!');    end if;
   select count(*) into dummy
    from check_datum
    where key = :new.key

          and (:new.begin_date between begin_date and end_date 
               or :new.end_date between begin_date and end_date )
          and :new.rowid != rowid;
    if dummy > 0 then
       raise_application_error(-20501,'Overlapping intervall found!');

    end if;
end;
/

Thenardier_at_POBoxes.com wrote:
>
> Hi All,
>
> In my program, the users need to enter two dates which
> represent the vacation range, which, supposedly, not
> to overlap with other vacation ranges. If there is vacations
> in the databases, I can check the newly input data against
> them. However, if there is not, i have to check the ranges
> record by record before i commit them. Then the problem
> comes:-
>
> 1. I can't use Post-Record, When-Validate, Post-Text-Item
> types of triggers cos i need to loop thru the records
> to check (it involves GO_RECORD(#);
> 2. I can't check in Key-Commit trigger before the command
> COMMIT cos i'll use RAISE FORM_TRIGGER_FAILURE to stop
> the program when there is overlapping date range. The
> reason is RAISing such failure activates a dialog box
> which ask me whether i want to save the changes. It
> gives me "Yes, No, and Cancel" options. So, the users can
> save those overlapping dates...
> 3. I can't use PL/SQL table which acts as an array in Form.
> Because my version PL/SQL doesn't support such useful
> function.
>
> Then what should i do? Pls rescue me! :P
> Thanks in advance!!!
>
> Thenard
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
 

-- 
Regards

Matthias Gresz    :-)

Received on Mon Jan 19 1998 - 00:00:00 CST