Trigger Trivia

  • nice article

    look like you've missed () in tip2

    if HOST_NAME='Somemachine' then return

    has to be

    if HOST_NAME()='Somemachine' then return

  • I frequently run into triggers others have created that don't realize that triggers need to account for set-based approaches, not just row-based approaches. When you see something like "SELECT @id = id FROM INSERTED" I end up slapping my forehead.

    Also the bit about disabling triggers is a good one that I use frequently. Great article.

  • Tony Rogerson has found that "DISABLING" triggers (at least in 2000) does not disable the underlying performance effect entirely

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/10/27/1248.aspx

    So do NOT trust on Disabling when performance is at stake. Drop them instead.

    Just my $0.02


    * Noel

  • hi Andy,

    found your article useful as we currently use triggers to audit some table changes through a third party product. I would also like to see something on performance with triggers, especially now that it hits the tempdb rather than the transaction log.

    Perhaps there's already an article on this?

    thanks...

  • I'll look around to see what I can find, might be fun to investigate!

  • For #6, you can also change the execution context of the trigger to execute as the execution context you need.

  • Great article! Lots of good info.

    I have a question about Tip #3. I understand that the TRIGGER will only fire once per batch... does an INSERT INTO with a SELECT statement for values constitute a single batch. Or would the TRIGGER fire for each row the SELECT returns? For example:

    INSERT INTO myTable

    ( col1, col2, col3 )

    SELECT col7, col8, col9

    FROM myOtherTable

    WHERE col7 = 1

  • Carlos, your example would only fire the Insert Trigger once regardless of how many rows the Select returns. All inserted rows will be available in the INSERTED table available in the trigger.

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

  • @jeff

    Thanks for the response. That was what I suspected.

    Same question, different scenario. What happens when one issues two separate INSERT statements in a single batch? For example:

    INSERT INTO myTable

    ( col1, col2, col3 )

    VALUES

    ( val1, val2, val3 )

    INSERT INTO myTable

    ( col1, col2, col3 )

    VALUES

    ( val4, val5, val6 )

  • carlos (3/26/2008)


    @Jeff

    Thanks for the response. That was what I suspected.

    Same question, different scenario. What happens when one issues two separate INSERT statements in a single batch? For example:

    INSERT INTO myTable

    ( col1, col2, col3 )

    VALUES

    ( val1, val2, val3 )

    INSERT INTO myTable

    ( col1, col2, col3 )

    VALUES

    ( val4, val5, val6 )

    Trigger fires twice. Again - it would fire once per operation, and the virtual inserted and deleted tables would hold all of the affected rows for the given operation.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • To put it a bit differently than Matt... INSERT triggers fire only once per INSERT statement no matter if one row or thousands of rows are affected. Same is true for UPDATE and DELETE triggers... they fire once for each instance of the SQL Statement no matter how many rows are affected.

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

Viewing 11 posts - 16 through 25 (of 25 total)

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