The Right and Wrong of T-SQL DML TRIGGERs (SQL Spackle)

  • Good article.

    I find this type of wrong triggers in a lot of databases.

    I also start with adding "set nocount on" at the start of each trigger.

    Otherwise, an update/insert/delete statement also outputs the rowcount of the updates/deletes/inserts done in the trigger ...

    You also want to check the trigger level,just to prevent infinite loops when triggers fire other triggers ...

    Bert

  • Nice article thanks!

    I've made that mistake before and seen others make it. It can be tricky to detect.

    A colleague told me of a nifty trick today.

    When releasing a trigger into the wild ensure all logic is encapsulated in a TRY / CATCH.

    In the catch, call sp_trace_generateevent and monitor any problems through profiler or extended events. (User configurable event).

    Handy information can be added e.g. where in the trigger the error occurred and what the error was.

    Also when altering a trigger, keep the original trigger code handy to roll it back if needed. This is assuming the original trigger wasn't causing problems.

  • DennisPost (4/8/2015)


    Nice article thanks!

    I've made that mistake before and seen others make it. It can be tricky to detect.

    A colleague told me of a nifty trick today.

    When releasing a trigger into the wild ensure all logic is encapsulated in a TRY / CATCH.

    In the catch, call sp_trace_generateevent and monitor any problems through profiler or extended events. (User configurable event).

    Handy information can be added e.g. where in the trigger the error occurred and what the error was.

    Also when altering a trigger, keep the original trigger code handy to roll it back if needed. This is assuming the original trigger wasn't causing problems.

    This is probably good advice and has some points of additional interest in it.

    One thing I do say for sure 100% of the time is if you do any (normally) atomic update, insert or delete to a table with a trigger, at least have error handling around it outside of the trigger.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I don't know why people insist on cluttering up triggers nor even most code with custom error handling especially when all most people do at best is rethrow the same error and at worst, totally obfuscate the natural error.

    --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)
    Intro to Tally Tables and Functions

  • DennisPost (4/8/2015)


    Nice article thanks!

    I've made that mistake before and seen others make it. It can be tricky to detect.

    A colleague told me of a nifty trick today.

    When releasing a trigger into the wild ensure all logic is encapsulated in a TRY / CATCH.

    In the catch, call sp_trace_generateevent and monitor any problems through profiler or extended events. (User configurable event).

    Handy information can be added e.g. where in the trigger the error occurred and what the error was.

    Also when altering a trigger, keep the original trigger code handy to roll it back if needed. This is assuming the original trigger wasn't causing problems.

    Hi Dennis. There are a few of points here that I am unclear on:

    1) Wouldn't encapsulating all modifications (i.e. across all, or almost all, objects) in TRY / CATCH be preferred? Why make it specific to triggers?

    2) What do you mean by "releasing into the wild"?

    3) What is nifty about raising a user-configurable event? Don't you have to be running a trace / XE session at that time in order to see it? Or am I misunderstanding how that works? Wouldn't simply logging the error to an ErrorLog table along with ERROR_NUMBER(), ERROR_LINE(), ERROR_MESSAGE(), etc not only give you more info, but also allow for seeing errors that occur when nobody is watching?

    4) And regarding the advice to keep the old version of the trigger around when altering, isn't this the assumed practice for all objects anyway? Are you suggesting that the DB objects (tables, indexes, constraints, procs, functions, views, etc) are not all in a version control system already? If not then they certainly should be, and not just the triggers.

    Take care,

    Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 5 posts - 16 through 20 (of 20 total)

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