reject overlapping date range within insert

Posted on 2011-04-18
Last Modified: 2012-06-21
I have a table

create table test_table(id number,start_date date not null,end_date date);

insert into  test_table values (1,sysdate, null);

Now if I want to insert another new row, I need a check condition, such that new row with the same id can't have overlapping date range.

Before inserting a new row the enddate will be updated and the new record start date must be greater than enddate of the exiting data.

Question by:vishali_vishu
    LVL 15

    Accepted Solution

    I would use the following algorithm:
    - insert the new record in a staging table;
    - make a select minus between staging table and final table - all records from staging, but only the records with end date = null from the final table;
    - on the select minus, all columns with the exception of the date fields should be compared;
    - for each record resulting from the select minus, do the following:
         - update on final table the enddate to sysdate - 1 where the natural key matches and the enddate is null;
         - insert a new record on the final table, with start date equal to sysdate and end date equal to null.

    Hope this helps.
    LVL 1

    Author Comment

    I know a way with triggers

    1. Row Level trigger (Before Insert): record the new rows into package variables (collections)

    2. Statement Level trigger(Before Insert): Read the package variable and check the condition and if condition not satisfied raise exception.
    LVL 51

    Expert Comment

    create a sp

    ins_rec(id, sd, ed)

    here check the table for existing overlapping values, if there is one, adjust the dates or raise an exception and use this sp to insert records...

    LVL 14

    Assisted Solution

    If you create an SP it should be fairly simple.

    Do let us know if you need our help in SP logic
    LVL 4

    Assisted Solution

    insert into  test_table (id,start_date)
    select l_id , l_start_date from dual where start_date > (select max(end_date) from test_table)

    above insert will insert values stored in variables l_id and l_start_date only if start_date is greater than max. end_date.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now