reject overlapping date range within insert

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.

Who is Participating?
Walter RitzelConnect With a Mentor Senior Software EngineerCommented:
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.
vishali_vishuAuthor Commented:
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.
HainKurtSr. System AnalystCommented:
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...

ajexpertConnect With a Mentor Commented:
If you create an SP it should be fairly simple.

Do let us know if you need our help in SP logic
devindConnect With a Mentor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.