I have a table with a start date range, an end date range, and a few other additional columns. On input of a new record, I want to automatically adjust any overlapping date ranges (shrinking them to allow for the new input). I also want to ensure that no overlapping records can accidentally be inserted into this table.
I'm using Oracle and Java for my application code. How should I enforce the prevention of overlapping date ranges and also allow for automatically adjusting overlapping ranges? Should I create an AFTER INSERT trigger, with a dbms_lock to serialize access, to prevent the overlapping data. Then in Java, apply the logic to auto adjust everything? Or should that part be in PL/SQL in stored procedure call? This is something that we need for a couple other tables so it'd be nice to abstract.
If anyone has something like this already written, please share :)
I did find this reference: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:474221407101
Here's an example of how each of the 4 overlapping cases should be handled for adjustment on insert:
= Example 1 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')
Input
(20, 50, 'A')
Gives
(0, 10, 'X')
**(20, 50, 'A')
**(51, 100, 'Z')
(200, 500, 'Y')
= Example 2 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')
Input
(40, 80, 'A')
Gives
(0, 10, 'X')
**(30, 39, 'Z')
**(40, 80, 'A')
**(81, 100, 'Z')
(200, 500, 'Y')
= Example 3 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')
Input
(50, 120, 'A')
Gives
(0, 10, 'X')
**(30, 49, 'Z')
**(50, 120, 'A')
(200, 500, 'Y')
= Example 4 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')
Input
(20, 120, 'A')
Gives
(0, 10, 'X')
**(20, 120, 'A')
(200, 500, 'Y')
The algorithm is as follows:
given range = g; input range = i; output range set = o
if i.start <= g.start
if i.end >= g.end
o_1 = i
else
o_1 = i
o_2 = (o.end + 1, g.end)
else
if i.end >= g.end
o_1 = (g.start, i.start - 1)
o_2 = i
else
o_1 = (g.start, i.start - 1)
o_2 = i
o_3 = (i.end + 1, i.end)
