Solved

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

  • 23 April 2019
  • 5 replies
  • 185 views

Userlevel 3
Badge +9
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?
icon

Best answer by Erwin Ekkel 25 April 2019, 15:28

View original

5 replies

Userlevel 3
Badge +9
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.
Userlevel 6
Badge +16
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.
Userlevel 6
Badge +16
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.
Userlevel 3
Badge +9
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.
Userlevel 6
Badge +16
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.

Reply