Nested triggers 2
-
Comments posted to this topic are about the item Nested triggers 2
Tom
-
tricky question for me. need more time to solve.:doze:
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!! -
Good question, although I didn`t take my coffee yet and it needed more concentration to read it properly (as most of the QoTD now are tricky), but I managed to solve it correctly.
Thx
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
It also might be because it is still early here, but what is the point exactly?
The view definition is such that...
. What is it exactly why it works? The fact that in the view only one table is used or...?
-
Mighty (5/8/2013)
It also might be because it is still early here, but what is the point exactly?The view definition is such that...
. What is it exactly why it works? The fact that in the view only one table is used or...?
Views can be "always updatable", "never updatable", or "sometimes updatable" (not official terms). All those apply to views without INSTEAD OF triggers, or to updates to the view from within the INSTEAD OF trigger (this is correct functionality, and required to make things work, especially for INSTEAD OF triggers on a table rather than a view. Inside an INSTEAD OF trigger, you can perform the action that causes the trigger to fire and it won't fire again. So you can e.g. have an instead of insert trigger on a table that splits the inserted rows into valid and invalid ones, logs the invalid ones in an error table, then inserts the remaining rows in the original table without firing itself and causing an infinite loop).
The view in this example is "always updatable"; updates to the x and id column translate to the underlying table; updates to y and z are lost.
An example of a view that is "never updatable" is a view that select constant values, or a view that aggregates data - rows is that view have no 1-to-1 mapping to rows in the underlying table.
Views based on a join are often "sometimes updatable". If you update a set of columns that all come from the same underlying table, the update is accepted as it can internally be translated to an update on a single table, but if you affect rows coming from multiple underlying table, you'll get an error.
For a view with an INSTEAD OF trigger, updatability rules are irrelevant, because the trigger is supposed to handle the updates. However, if you update the view from within the INSTEAD OF trigger, the updatability rules apply again, because (as explained above) the INSTEAD OF trigger will be bypassed.
That being said - when using an INSTEAD OF trigger on a view, I personally think that the modifications made inside that trigger should always address the underlying table, not the view. Makes the code much easier to understand! The only situation where I would want an INSTEAD OF trigger to modify the object it is defined for would be in the case of an INSTEAD OF trigger on a table, not a view. And INSTEAD OF triggers on a table should be used very rarely.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
This was removed by the editor as SPAM
-
Awesome, Tom. Thank you for the post.
(as soon as I saw the condition, "=ceiling..." i was sure ... it was you 🙂 )
Very smart question and explanation.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
Hugo Kornelis (5/8/2013)
Mighty (5/8/2013)
It also might be because it is still early here, but what is the point exactly?The view definition is such that...
. What is it exactly why it works? The fact that in the view only one table is used or...?
Views can be "always updatable", "never updatable", or "sometimes updatable" (not official terms). All those apply to views without INSTEAD OF triggers, or to updates to the view from within the INSTEAD OF trigger (this is correct functionality, and required to make things work, especially for INSTEAD OF triggers on a table rather than a view. Inside an INSTEAD OF trigger, you can perform the action that causes the trigger to fire and it won't fire again. So you can e.g. have an instead of insert trigger on a table that splits the inserted rows into valid and invalid ones, logs the invalid ones in an error table, then inserts the remaining rows in the original table without firing itself and causing an infinite loop).
The view in this example is "always updatable"; updates to the x and id column translate to the underlying table; updates to y and z are lost.
An example of a view that is "never updatable" is a view that select constant values, or a view that aggregates data - rows is that view have no 1-to-1 mapping to rows in the underlying table.
Views based on a join are often "sometimes updatable". If you update a set of columns that all come from the same underlying table, the update is accepted as it can internally be translated to an update on a single table, but if you affect rows coming from multiple underlying table, you'll get an error.
For a view with an INSTEAD OF trigger, updatability rules are irrelevant, because the trigger is supposed to handle the updates. However, if you update the view from within the INSTEAD OF trigger, the updatability rules apply again, because (as explained above) the INSTEAD OF trigger will be bypassed.
That being said - when using an INSTEAD OF trigger on a view, I personally think that the modifications made inside that trigger should always address the underlying table, not the view. Makes the code much easier to understand! The only situation where I would want an INSTEAD OF trigger to modify the object it is defined for would be in the case of an INSTEAD OF trigger on a table, not a view. And INSTEAD OF triggers on a table should be used very rarely.
Great explanation, Hugo, this gives even more grip on the subject.:-)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
Stewart "Arturius" Campbell (5/8/2013)
This one got the grey matter churning a bit.Thanks, Tom
+1 for sure. I wasn't sure I was going to get it right without any caffeine intake to boost brain capacity. As always Hugo, thanks for the explanation.
Everything is awesome! -
Mighty (5/8/2013)
It also might be because it is still early here, but what is the point exactly?The view definition is such that...
. What is it exactly why it works? The fact that in the view only one table is used or...?
Hugo's explanation mostly covers it. In addition to what Hugo says, you need to know that MS SQL Server has a rule that delete or insert on a view which involves more than one table can't be done except by an instead of trigger which directly accesses the base tables - so if an instead of delete (or instead of insert) trigger on a view does a delete (or insert) on that view it must not be a view which involves more than one table. According to BOL this applies to all views involving more than one table, even union views (where there is no good reason to have such a rule). So yes, the fact that only one table is used is an important part of the reason it works, as you suggested.
Of course if two tables are used in a join view, delete will almost always be ambiguous, so it makes sense to insist on a trigger to say what is to be done to the base tables.
Not all single table views can be deleted directly - think of a view like this one (which is covered in Hugo's explanation)
create view zv on tab1 asselect tab1.x, count(*) as k from tab1 group by tab1.x
go
Now suppose you get a command "delete from zv where k = 3" and there's a row 'abcde',3 in tab1; which of the three rows in tab1 with 'abcde' in the x column should you delete? You don't know, so there has to be an instead of trigger which tells the system which base table row(s) to delete. So using only one table is only part of the story.
Tom
-
Nice back to basics question, thanks!
(Do I get an extra point for 'most inappropriate comment'? 😉 I enjoyed the explanations, my takeaway is avoid triggers in general, especially on views.)
-
Another great one from Tom. Thanks to both Tom for the question and to Hugo for his analysis.
-
Really had to think about this one. Thank to both/all for input and explanation.
Not all gray hairs are Dinosaurs!
-
Great question.....
-
Good question - thanks had to think twice before answering
Hope this helps...
Ford Fairlane
Rock and Roll Detective
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply