Post reply

Audit Trial help

  • October 15, 2018 at 10:15 am

    #379568

    The new Trigger Table should hold  new values and old values in the following way.  Please see example table pic. 

    thanks.

  • October 15, 2018 at 10:21 am

    #2009655

    Duplicate post.

    https://qa.sqlservercentral.com/Forums/2002068/SQL-server-Audit-Trigger-for-INSERT-UPDATE-DELETE

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • October 15, 2018 at 11:24 am

    #2009668

    Michael, I got few different versions of SQL servers. 
    thanks.

  • October 15, 2018 at 4:37 pm

    #2009769

    SQL learner22 - Monday, October 15, 2018 10:15 AM

    The new Trigger Table should hold  new values and old values in the following way.  Please see example table pic. 

    thanks.

    As a general rule, people don’t like to open pictures in SQL forum. We expected you to follow the rules, and post DDL and perhaps sample data. Then would like to see what DML you tried to hear what your problems were. It’s very hard to program from a vague general narrative. I hope you’re not working in a shop that makes you do that.

    Afterbeing in this business for a few decades, I’ve come to theconclusion when you want to an audit, the best thing to do is to buya third-party package. It will almost certainly do a better jobanything you could write. It will be updated and supported by itsvendor. And when you go to court, they’ll be the ones that areliable. Remember, we live in a world in which “ROI†really means“risk of incarceration†today.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • October 15, 2018 at 7:24 pm

    #2009787

    SQL learner22 - Monday, October 15, 2018 10:15 AM

    The new Trigger Table should hold  new values and old values in the following way.  Please see example table pic. 

    thanks.

    I've got one of these damned things in my database from predecessors.  How wide is the original table in columns and what is the PK for that original table?

    And, if someone suggests using CLR triggers for this, tell them to shove off because the CLR trigger needs to materialize both the INSERTED and DELETED logical tables and that makes things horribly slow, especially on wide tables.  When I first found the CLR triggers in my system, it was taking more than 4 minutes to update just 4 columns on only 10,000 rows.  I know I'm slipping but using hardcoded triggers (built by a stored procedure I made), that now runs at about 400ms (which is still a bit slow but one hell of a lot better than 4 minutes!!!)/

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 16, 2018 at 8:29 am

    #2009880

    I admit I did not follow the standard way of posting Topics to the forum.   I accepted.
    I work at a small shop where I am a DBA and since they do not have a developer I try to help with making stuff that normally developers take care of it. I do try to help but sometimes it’s challenging.
    Thanks.

  • October 16, 2018 at 9:33 am

    #2009899

    SQL learner22 - Tuesday, October 16, 2018 8:29 AM

    I admit I did not follow the standard way of posting Topics to the forum.   I accepted.
    I work at a small shop where I am a DBA and since they do not have a developer I try to help with making stuff that normally developers take care of it. I do try to help but sometimes it’s challenging.
    Thanks.

    Understood and not a problem.  The question remains, though.  How many columns in the tables that you wish to audit?  That will determine the type of audit table that you need.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 16, 2018 at 1:30 pm

    #2009946

    we should be able to capture any changes to any column in the table.  all. 

    thanks.

  • October 16, 2018 at 1:51 pm

    #2009951

    SQL learner22 - Tuesday, October 16, 2018 1:30 PM

    we should be able to capture any changes to any column in the table.  all. 

    thanks.

    Understood but I need to know how many columns the table has.  If you do what is known as "whole row" auditing on a 147 column wide table that has only about 1 row per page and you typically update only a couple of columns at a time, you'll have a huge and terrible waste of disk space and may want to consider "column level" auditing, instead.

    So... how many columns does/do the table(s) that you want to audit have?

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 18, 2018 at 4:00 pm

    #2010320

    Steve,
    the table got about 32 columns and anyone of these columns can be changed or the whole row.

    thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

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