Trigger Trivia

  • Comments posted to this topic are about the item Trigger Trivia

  • Good synopsis of uses, Andy!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Short but very informative. Thanks!

  • By chance the disable trigger hint came just when I needed it. Thanks for the nice synopsis you won't find somewhere else.

  • if you need all trigers disabled, you can specify 'all' instead of triggername. i found it useful

  • Great article, Andy - thanks. Can I just ask a question about Tip#4?

    If you want to really make sure no resultsets occur you can use spconfigure to set disallow results from triggers to off.

    Is this a typo and did you mean to say "on"? Also, if you do this, you'll get an error message every time a trigger tries to return a result set and so you'd need to add some error-handling code to your trigger and/or application.


  • John, you're right, it should be ON. Calling it a typo is generous!

  • Dragos, I don't think I knew that about ALL, thats a nice shortcut.

  • Wow!

    The tip about DDL and CLR triggers was great!

    It's too bad we don't see more articles like this!

    Short, Sweet, and useful!

  • Andy, that's the one i used most times... if not always. glad if it helps

  • Very nice job. Short, sweet and to the point.


  • Outstanding, Andy. Keep 'em coming.

  • Regarding "Tip #6 - Triggers execute with the permissions of the user", this is not completely correct. Given the example with tables Contact and ContactHistory and a trigger on that Contact table that references the ContactHistory table, the security rules for SQL Server 2000 are:

    If the ContactHistory table has the same owner as the Contact table, then the user does not need rights to the ContactHistory table.

    If the ContactHistory table has a different owner than the Contact table, then the user does need rights to the ContactHistory table.

    For SQL Server 2005, substitute "Schema" for "Owner".

    SQL = Scarcely Qualifies as a Language

  • Tip #9 opened my eyes. Triggers on views!!! I never thought to check if such a thing was possible. I have some apps that directly modify data and I was pondering how I could impliment a better schema without trashing the existing app. Now I can sneak in my new framework and my crummy application will be none the wiser!

  • It's important to fix #6 about permissions - that does sound misleading, especially that more often than not folks will be updating table in the same schema.

    The one about "selective updates using hostname" has a security issue about it - the host name can be set as a part of connection string. E.g. this is not the attribute one can rely on.

Viewing 15 posts - 1 through 15 (of 25 total)

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