SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nested triggers 2


Nested triggers 2

Author
Message
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14822 Visits: 12238
Comments posted to this topic are about the item Nested triggers 2

Tom

Danny Ocean
Danny Ocean
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1668 Visits: 1549
tricky question for me. need more time to solve.Doze

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
Hany Helmy
Hany Helmy
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3107 Visits: 1117
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
Mighty
Mighty
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4452 Visits: 1705
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 Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11388 Visits: 12007
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9223 Visits: 7283
This one got the grey matter churning a bit.
Thanks, Tom

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Raghavendra Mudugal
Raghavendra Mudugal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2158 Visits: 2958
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.
Raghavendra Mudugal
Raghavendra Mudugal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2158 Visits: 2958
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.
Dana Medley
Dana Medley
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2530 Visits: 1696
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!
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14822 Visits: 12238
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 as 
select 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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search