• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

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.

0
vishali_vishu
Asked:
vishali_vishu
3 Solutions
 
Walter RitzelSenior 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.
0
 
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.
0
 
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...

0
 
ajexpertCommented:
If you create an SP it should be fairly simple.

Do let us know if you need our help in SP logic
0
 
devindCommented:
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.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now