Changing the order of instead of trigger procedures when executing source code

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?

Be the first to reply!

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.

I think you just answered your own question.

It's also possible to create a procedure with multiple templates and assign the necessary template to each view.

Sign up

Login to the Thinkwise Software Community

Scanning file for viruses.

This file cannot be downloaded