March 11, 2015 at 8:01 am
I have a view made up of a few base tables and another view. I have created an INSTEAD OF trigger on this view, but it doesn't seem to fire whenever a new record shows in the view. The purpose of the trigger is to insert a sister record in a table whenever a new record shown in the view. Here's the catch, the table that the trigger is supposed to insert into is not a base table within the view and the view is not an updatable view. My question is... Do INSTEAD OF triggers only affect the base table(s) within the view and does the view itself have to be an updatable view? Thanks.
March 11, 2015 at 8:15 am
If there's an INSTEAD OF trigger on the view, then when the view is the target of an data modification the trigger is fired replacing the data modification. Hence the view doesn't need to be updatable, as the data modification actually never occurs against the view, the trigger runs instead.
The trigger will only fire if the view is the target of the data modification. If the base tables are inserted/updated/deleted, the trigger won't fire.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2015 at 9:25 am
Thanks for the quick reply GilaMonster. Your explanation cleared up some major confusion and gave me a better understanding of INSTEAD OF triggers! I've looked all over the place and can't really find a good explanation of the trigger type.
Is there a way to insert a record into a table for every new record in the view?
March 11, 2015 at 10:04 am
A trigger on the base table for the view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply