When you have an indexed view, does the view get updated before or after triggers on the base table(s) fire? I ask this because in this question, it is stated that "The indexed view is updated after all triggers on the base tables are executed.", yet according to my testing, the view is updated first. Is there any definitive documentation on this?
Asked
Active
Viewed 27 times
1
-
I suspect the answer lies in transaction isolation i.e. all-the-things are happening in a single transaction so they're all ~~done~~ completed at once. However depending on whether you are opting to read uncommitted data or not will change the _observed_ results – gvee Oct 29 '21 at 07:36
-
Without testing I would think that you are correct and the linked answer is wrong. The indexed view updates get integrated into the execution plan for the base table updates. The trigger gets its own execution plan which is fired after that one completes but still in the same transaction – Martin Smith Oct 29 '21 at 09:49
-
1That answer referred to an `instead of` trigger, which may be different. You are definitely right about `after` triggers. If you check the query plan you will see I'm right, the indexed view is updated at the same time as the base table – Charlieface Oct 29 '21 at 10:42