Hi, our billing application has a ASP web front-end. Back-end is a 12c DB. Our data-model is EAV.
This is a simple case of validating 2 attributes belonging to the same entity.
Since this is an EAV, for our CUSTOMER entity the attributes are stored in separate rows.
So, I have simplified our problem like this:
Front-end shows the customer like this:
Customer NO: 123 (This is an internal number which we generate using an Oracle Sequence. Read-only for user)
Customer Name: Rohan Braga
Customer Type: Domestic (This is a drop-down with Domestic and Industrial)
Heating Unit Code: I123 (User an enter values here)
The above information is stored in our DB in 2 rows in the CUSTOMERS_ATTRIBUTES table:
PK customer_no attrib attrib_char attrib_num attrib_date
109 123 cus_type D
111 123 heating_unit_code I123
We need to make sure that the Heating unit code starts with D or I.
So, the above data is wrong. Since the Heating Unit Code starts with I for a Domestic customer.
We want to do a validation and fail it in the front-end if the user enters wrong data.
Our framework has after rows triggers that fire for every attribute. All fires in the same session when you
enter data to both attributes.
What is the best way to do the validation?
My idea is to code the same validation logic in the 2 SPs that are called by the triggers and fail if the
validation fails.