August 13, 2019 at 1:37 pm
Is it possible to create a trigger on a table for a specific column that will log the user who modified column and the sql text used to update the column? I'm trying to track down where changes to a field is coming from.
Or can SQL Server profiler do what I'm looking for?
August 13, 2019 at 2:35 pm
profiler might be better for this since you want the sql text
i'm assuming it's a data change not a structure change you are auditing? if it's a structure (DDL) change then you can also use a database trigger
MVDBA
August 13, 2019 at 2:46 pm
Yes, just trying to catch a data change, who changed it, and the SQL text.
August 13, 2019 at 3:35 pm
if you set your profiler trace to write to a table rather than a file but don't execute it, ...just script it out
once you create the trace in script form you can use sp_tracesetstatus to stop or start your trace (while also being able to query the results in SQL format)
I've found that this works best for me as I don't have to have profiler running (but I often forget to stop and drop the trace)
MVDBA
August 14, 2019 at 11:34 am
We're on SQL Server 2016, yes?
Don't use Profiler and Trace Events. They are old school. Worse yet, filtering on them is horrible. Even though you apply a filter (only look at this query, or this table, as you're asking for), Trace still captures every single call, using all the memory and CPU necessary to capture all the calls, then filters after the fact.
Instead, use Extended Events. You're on SQL Server 2016. You're probably using functionality written since 2008R2, right? Well, Extended Events is how you have to monitor that functionality. There's no reason to not use it to monitor queries, etc.. In fact, there are lots of good reasons to use it. Filtering is just one. Instead of filtering after capture, Extended Events operates within the OS of SQL Server and filters before capture. The more granular you make your filters (just this database, just this object), the lower the load it actually places on the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 15, 2019 at 4:35 pm
If your users are connecting directly to the database when making changes, you could add modified_by and modified_on columns defaulting to suser_name() and sysdatetime() respectively. If you users are connecting through an application to make changes, the application would have to handle the tracking of who is making the change as applications typically use an application login. Also look into temporal tables to create an audit trail as appropriate.
August 20, 2019 at 9:29 pm
I'll definitely look into extended events. Thanks.
August 21, 2019 at 7:33 pm
An AFTER UPDATE trigger on the table would capture who, at least as well as you can within SQL Server. You can use "UPDATE(column_name)" to limit it to only processing for a single column. When capturing the user, consider using ORIGINAL_LOGIN() rather than CURRENT_USER or equivalent if people log into SQL as themselves.
Getting the SQL text would be trickier, but it could be possible. You can use @@SPID to get the current process id, query sys.dm_exec_requests for that id, and from that view, use the sql_handle and/or plan_handle to finally call sys.dm_exec_sql_text to get the SQL text. I haven't tested this out recently, but it could work, particularly for only a single column being updated.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 22, 2019 at 8:41 am
I'd avoid the trigger
just based on the fact that it will fire (even if it does nothing) on every modification action to the data on that table (depending on how you code the trigger)
It's also so easy to get a trigger wrong and forget that you can have multiple records affected.
I have a system written 10 years before i started here. we can't update more than one row at a time because the trigger on the table calls a proc and several functions. that means we have to put updates into a cursor.......many layers of bad.... trigger, cursor, function
MVDBA
August 22, 2019 at 3:27 pm
Of course bad triggers can be written. Bad stored procedures can also be written, but that doesn't mean we should quit writing stored procedures (agreed?). Yes, take great care to write triggers properly, but don't just throw them away, they are a valuable tool when needed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 23, 2019 at 5:45 pm
Of course bad triggers can be written. Bad stored procedures can also be written, but that doesn't mean we should quit writing stored procedures (agreed?). Yes, take great care to write triggers properly, but don't just throw them away, they are a valuable tool when needed.
I totally agree. +1000000!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply