TRIGGERs are often a subject of contention. Some data professionals have strong opinions about them, especially on the “they’re bad!” side. But like anything, they’re a specific kind of tool that has a place in a specific problem. One of the common scenarios for their usage is what we call an “Audit trail” — a running log of any & all changes made to a table or data-set (or even a database schema, as RedGate DLM Dashboard employs).
Yes, there are newer product features built around this very requirement, such as
Change Data Capture(CDC) and
Temporal Tables, but they require particular versions and editions of the product, and not every environment is going to be there. So we need something native and compatible to the lowest common/supportable denominator.
Like most areas of the database environment, triggers are often poorly documented. So what happens when we need to review all the triggers in a database and show what tables they’re on & what columns they’re “tracking” (sticking with the audit-trail example)? Or, more generally, you could say, we want to find all table & column dependencies of our triggers. Well sure, we could use the SSMS Object Explorer GUI — find the table, the triggers, right-click and “Show Dependencies”.. but that’s not good enough, for two reasons: A) it doesn’t get down to the column level, and B) it’s not script-able.
System catalogs and DMVs to the rescue!
Disclaimer: this is heavy on MS Docs links. There was a StackOverflow Q/A that helped point me in that direction, but sadly I lost the tab and didn’t save the link.
In this script, I mostly focus on the
UPDATE triggers, because they’re the most heavy-handed in terms of auditing. Whereas with a typical
DELETE, the triggered audit action is basically “Hey, a row was inserted/deleted, here’s its primary key” — and sure you can get fancier and log what all the values were in the entire row — those are fairly straightforward. It’s basically “all or nothing”. In contrast, with an
UPDATE audit, we want to know just what was updated, and from what (old value) to what (new value).
So the goal of documenting the dependencies is to see which columns we care about — because, inevitably, there are some columns that we either “don’t care about” for auditing, or some that just plain don’t change (by their nature — they’re an
identity or a primary key, or controlled via other means). In turn, this helps us understand how to reproduce these audit mechanisms when a better technology comes along — such as
temporal tables, as alluded to earlier.
We start with the system catalog views
sys.triggers. This establishes our primary relationship — a
trigger ‘lives’ on (is attached to) a single
table. Then we immediately step into murky water. A trigger, like other programmable objects, has a definition script, which you can view in a myriad of ways — Object Explorer,
sys.syscomments (deprecated), or
OBJECT_DEFINITION(OBJECT_ID). So if we really wanted to go spelunking, we could just read the trigger’s definition and see what columns it deals with. But after about 2 or 3 of those, our eyes are going to glaze over and our fingers will start to twitch. We need something better, something that can handle hundreds of triggers and output a nice unified result!
Oh wait, what’s this? Object Explorer has a “View Dependencies” function.. aw, bummer, it doesn’t show columns. Just tables. As we know, most of the GUI functionality is driven by existing system meta-data/catalogs/dmv’s, so there’s definitely something that keeps track of these dependencies. Let’s hazard a guess…
sys.sql_expression_dependencies? The former is deprecated, the latter doesn’t give us a way to get to columns either. But thankfully, on the docs, the deprecated note points us toward the correct path:
Now we’re cooking with gas! So how do I use these suckers? Well, there’s a couple intermediary joins I need to go thru, and in fact these are functions, not views, so we need to know the proper arguments. But fear not, I have done the hard work for you! Check out the code and let me know what you think.
But wait, there’s more! There’s a slight monkey-wrench here. You see, in the column dependencies, we’re getting both the “audited” columns and the “key” columns. But we’d like those to be distinguished from each other. When we document and/or replicate the auditing using newer technology, the primary keys aren’t what we need to track (they’re in a sense “given”, known) — we need to know which columns are “audit-able” and which aren’t. Hence the two queries with a
So there you have it, documenting some audit-trail triggers. Or triggers in general. Complete with the tables & columns on which they depend. Enjoy! ‘Til next time folks.