vishali_vishu
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.