Nested triggers 2

  • TomThomson

    SSC Guru

    Points: 104773

    Comments posted to this topic are about the item Nested triggers 2

    Tom

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    tricky question for me. need more time to solve.:doze:

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Hany Helmy

    SSChampion

    Points: 13488

    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

    SSCrazy Eights

    Points: 8815

    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

    SSC Guru

    Points: 64685

    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

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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

    SSChampion

    Points: 10658

    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

    SSCertifiable

    Points: 6764

    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!

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • call.copse

    SSCoach

    Points: 17206

    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.)

  • Revenant

    SSC-Forever

    Points: 42467

    Another great one from Tom. Thanks to both Tom for the question and to Hugo for his analysis.

  • Miles Neale

    SSChampion

    Points: 13147

    Really had to think about this one. Thank to both/all for input and explanation.

    Not all gray hairs are Dinosaurs!

  • Bangla

    Hall of Fame

    Points: 3137

    Great question.....

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    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 21 total)

You must be logged in to reply to this topic. Login to reply