using trigger for tracing

  • Jackie Lowery

    SSCommitted

    Points: 1739

    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 5 days, 10 hours ago by  Jackie Lowery.
  • MVDBA

    SSC-Insane

    Points: 20353

    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: 1739

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

  • MVDBA

    SSC-Insane

    Points: 20353

    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: 395394

    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: 10234

    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.

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

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