We have a view 'person_look_up' with an instead of trigger (IOT) that inserts records into the view 'relation_address_view' which also has it's own IOT.
When executing source code (via a 'Full' upgrade) we get an error because the IOT on the 'person_look_up' is created before the IOT of 'relation_address_view'.
I tried changing the 'Generation order no' of the views and the IOT procedures but it didn't change anything.
View:
relation_address_view: generation_order_no = 100
person_look_up : generation_order_no = 101
IOT procedures:
relation_address_view : generation_order_no = 100
person_look_up : generation_order_no = 101
How do we solve this issue?
Best answer by Erwin Ekkel
Then create a subroutine with the logic from the IOT of relation_address_view. You can then add this subroutine in both the IOT of person_look_up and in relation_address_view. This will have the same effect but will remove the need to insert into the view.
Why would you update the view and not just insert into the base tables?
Apparently your view relation_address_view consists of multiple tables. Instead of inserting into the view, insert into the base tables in separate insert statements.
We did this because the iot (insert) of relation_address_view creates a new address and links it the relation.
The iot (insert) of person_look_up view has to create a relation first before executing the same statements as the iot (insert) of relation_address_view.
In the 'low code' mindset we would like to reuse the iot (insert) from the relation_address_view with the inserted values of person_look_up.
Then create a subroutine with the logic from the IOT of relation_address_view. You can then add this subroutine in both the IOT of person_look_up and in relation_address_view. This will have the same effect but will remove the need to insert into the view.
There's a pretty negative side effect of using a subroutine ('procedure' type) within an instead of trigger. If applied to an instead of trigger a cursor or while loop will be necessary because the inserted table may contain multiple rows. This could have a great impact on the performance of the trigger.
So I don't think it's the best solution.
It's also possible to create a procedure with multiple templates and assign the necessary template to each view.
The developer who has created the instead of trigger already copied the complete code of relation_address_view into the IOT template of person_look_up. But that does mean we have to maintain the exact same code in multiple places.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
