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)