Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Nested triggers 2 Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 9:16 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:42 PM
Points: 8,567, Visits: 9,071
Comments posted to this topic are about the item Nested triggers 2

Tom
Post #1450390
Posted Tuesday, May 7, 2013 11:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
tricky question for me. need more time to solve.



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1450409
Posted Wednesday, May 8, 2013 12:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:38 AM
Points: 1,139, Visits: 520
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
Post #1450416
Posted Wednesday, May 8, 2013 2:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 3,201, Visits: 1,239
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...?
Post #1450441
Posted Wednesday, May 8, 2013 2:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 5,930, Visits: 8,178
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
Post #1450445
Posted Wednesday, May 8, 2013 2:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 3,864, Visits: 5,009
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”
Post #1450450
Posted Wednesday, May 8, 2013 6:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 854, Visits: 1,933
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.
Post #1450523
Posted Wednesday, May 8, 2013 7:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 854, Visits: 1,933
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.
Post #1450546
Posted Wednesday, May 8, 2013 7:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 1,829, Visits: 1,364
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!
Post #1450572
Posted Wednesday, May 8, 2013 7:49 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:42 PM
Points: 8,567, Visits: 9,071
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
Post #1450574
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse