How to create continuous fields in a database
I am trying to create a table name "title" in my database that has two fields named "lower_bound" and "upper_bound". I want to create a trigger, for example when trying to add a contiguous range by automatically adding it to the previous row. For example, consider that this table has two rows such as:
id | lower_bound | upper_bound
------------------------------
1 | 10 | 20
------------------------------
2 | 50 | 70
------------------------------
I want that if I insert a row with the following values:
lower_bound = 21
upper_bound = 30
Instead of inserting this line, I add it to the first line and update its field as shown below:
id | lower_bound | upper_bound
------------------------------
1 | 10 | 30
------------------------------
2 | 50 | 70
------------------------------
Is this possible with a trigger?
My solution was that I use the BEFORE INSERT trigger and check my requirements, and if it was possible to update, I update my row.
There were two problems:
- I am unable to reverse the insert process without raising the error, which is not what I want.
- I cannot change the value of the inserted row id as the updated row id.
source to share
Can you change your schema? Implement two tables, low_bound and high_bound, referencing each other by key. Create a materialized view of the join of two tables. Create a before insert trigger on the materialized view that implements validation and updates the low_bound and high_bound values as needed.
source to share