Generic DML/DDL Triggers and performance implications

Discussion in 'microsoft.public.sqlserver.server' started by pxb, Nov 6, 2008.

  1. pxb

    pxb Guest

    To perform auditing in SQL 2005 at Select/Insert/Delete level I think I need
    to write DML Triggers to do that (Trace cannot handle it), I see 2 challenges:

    (1) The effort to write triggers for each entity
    (2) Performance issues as this is not a product level support

    For (1) I like to explore on the followings:
    - Are there any tools that can auto-generate these triggers?
    - Any recommendations or samples on writing a generic triggers for that?

    For (2) I like to get advice from experts who has done that before, any tips
    to avoid performance issues?

    pxb, Nov 6, 2008
    1. Advertisements

  2. The only way to catch SELECT is with SQL Trace. DML triggers can catch
    Insert/Update/Delete activity but not SELECT. I have a script that will
    create the audit tables and triggers for all Insert/Update/Delete activity.
    DDL Triggers will audit changes to the database or server depending on the
    scope from DDL operations.

    Since you mention Audit, for SOX, we have a trace running 24 hrs a day that
    monitors the activity performed on the databases that are considered in
    scope. Those traces are collected and emailed to the CIO daily. SQL 2008
    has the built in Audit, which is built on top of the Extended Events Engine
    that allows you to get Selects as well.
    Jonathan Kehayias, Nov 6, 2008
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.