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

Question

Post reply

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

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

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

Bert De Haes Hall of Fame Points: 3091 More actions · Answer 1

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

DennisPost SSCrazy Points: 2691 More actions · Answer 2

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.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

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)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 5

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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR