• dastagiri16 - Saturday, February 24, 2018 12:03 AM

    Hi Team,
    I have created one single trigger to capture information modified by the insert/update/delete commands.

    is there any performance difference if we create 3 separate triggers(like after insert ,after update ,after delete) ?

    Thanks in Advance.
    Dastagiri D

    Maybe... "It Depends" on how you write them.  I've not done such a study but SQL Server is pretty fast about deciding what the triggering event was and, since any given event can only be one of the three (Insert, Update, or Delete), it may cost you more to write code in a single trigger to make a decision as to which event occurred than if you had three different dedicated triggers.

    The real key is to ask what the/these triggers will be used for.  For example, if you're using triggers to populate audit tables, then it's a serious mistake to use the "INSERTED" logical table of any trigger and you certainly should not have a separate "INSERT" trigger because auditing inserts instantly doubles your storage requirements for what will likely become the biggest table in your entire database.

    With that in mind, what are you using this/these triggers for?

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