SQL Trace or SQL Trigger

Posted on 2010-01-06
Last Modified: 2012-08-14
Hi to all,

I am trying to trace insert, delete or updates on 4 tables in my SQL database. I am not very good with SQL pro-filer and I don't know how to create a trace on only 4 tables. The trace I currently have shows ALL SQL queries on the whole database and the is to much. I read that a trigger might be a better way to go, but I also do not know how to create a trigger to accomplish that.

The tables I want to trace is: PurchaseOrder and PurchaseOrderDetails. Workorder and WorkorderDetails.

What is the best way to go and is there a sample or walk through that I can use or work with.

Thanks to all
Question by:ALawrence007
    LVL 26

    Expert Comment

    what do you want to audit about these tables?
    LVL 15

    Accepted Solution


    Author Comment

    I am trying to see what the insert, update and delete statement looked like as well as the values was inserted, updated and deleted.
    LVL 3

    Assisted Solution

    You can still use the results of your Trace to examine your insert, update, etc. statements.  

    Are the results of your trace in a file, or in a table?  In the trace, there is probably a column called TextData.  Add the below like to your WHERE clause and you should have a comprehensive list of all statementents on that table, as well as the values.

    WHERE TextData Like '%MyTableName%'

    If your trace has been saved to a file(s), you can query it using this system function:
    SELECT *
    FROM ::fn_trace_gettable('FullPathAndFileName.trc', default)


    Author Closing Comment

    It took me a while, but I got there in the end.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how the fundamental information of how to create a table.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now