using trigger for tracing

  • Jackie Lowery

    SSCommitted

    Points: 1757

    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?

    • This topic was modified 3 months, 3 weeks ago by  Jackie Lowery.
  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21170

    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

  • Jackie Lowery

    SSCommitted

    Points: 1757

    Yes, just trying to catch a data change, who changed it, and the SQL text.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21170

    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)

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setstatus-transact-sql?view=sql-server-2017

    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

  • Grant Fritchey

    SSC Guru

    Points: 395623

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Joe Torre

    SSChampion

    Points: 10239

    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.

  • Jackie Lowery

    SSCommitted

    Points: 1757

    I'll definitely look into extended events. Thanks.

  • ScottPletcher

    SSC Guru

    Points: 98203

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21170

    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

  • ScottPletcher

    SSC Guru

    Points: 98203

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Jeff Moden

    SSC Guru

    Points: 995093

    ScottPletcher wrote:

    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


    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 11 (of 11 total)

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