how to create continuous fields in database

Question

I am trying to create a table name "title" in my database that has two field named as "lower_bound" and "upper_bound". I want to create a trigger on it such as when trying to add a continuous range, automatically it append it to the previous row. For example consider we have two row in this table like the follow :

id | lower_bound | upper_bound
------------------------------
1  | 10          | 20
------------------------------
2  | 50          | 70
------------------------------

What I want is that if I insert a row with follow values:

lower_bound = 21
upper_bound = 30

Instead of inserting this row, I append it to the first row and update its field like the follow:

id | lower_bound | upper_bound
------------------------------
1  | 10          | 30
------------------------------
2  | 50          | 70
------------------------------

Is this possible with a trigger?

My solution was that I use BEFORE INSERT trigger and do my requirement checking and if it was update able then I update my row.

There were two problems:

1. I can't cancel inserting process without raising an error which is not what I want.
2. I can't change the value of inserted row id as updated row id.

Answer 1

Can you change your schema? Implement two tables: low_bound and high_bound referencing each other by key. Create a materialized view on the join of the two tables. Create a before insert trigger on the materialized view that implements the check and updates the low_bound and high_bound as required.