1

I created an indexed view with a INSTEAD OF trigger on it, but it's not firing when inserts happen at the base table level.

This is the plan that a normal insert generates (note the clustered index insert on VW_X.VW_CI which is the clustered index representing the view)

SQL Server Plan image

Is there any way I can track inserts on a view despite the fact that inserts don't really happen there, but at the base tables that create the view?

1 Answer 1

4

The base table insert plan incorporates operations necessary to keep the indexed view synchronized with the base table as defined by the view definition. This part of the plan is automatically generated and cannot be disabled, or made to call the instead of trigger logic on the view instead. The contract of an indexed view is that it will always materialize the query stored in the view.

The main purpose of instead of triggers on views (indexed or otherwise) is to make them updatable when they wouldn't be otherwise. The question doesn't explain what you need to achieve, but it seems an indexed view is not the right mechanism. You might need a separate table instead of a view. Please ask a follow-up question if you want help solving the underlying problem, and ideally provide a self-contained reproduction script.

1
  • Thanks. The underlying problem I was trying to solve was to setup change data capture on a view. Since you cannot enable it on a view, my idea was: base tables get insert/updates/deletes --> indexed view triggers transfers records to a physical table like the view --> the physical table has CDC enabled, so I can easily track changes for a data warehouse. I thought that was a good idea to test, but looks like I'll have to find another way Commented Mar 3, 2020 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.