Post reply

Microsoft SQL triggers on columns

  • April 20, 2014 at 3:54 am

    #396630

    Dear All

    I have one table namely consumer with approx 50 columns.

    I have created one same table with audit prefix including 2 more column for action n timestamp fields.

    My question is that if user change only 10 column data at a time: i want to add only that particular column data rather to add entire row. Currently i am adding entire row in audit table but now scnario is change to update only updated column data.

    If any one has already worked on that please do Help me.

    Thank you in advance.

  • April 20, 2014 at 4:17 am

    #1707312

    I posted some code in this thread a while back

    http://www.sqlservercentral.com/Forums/Topic1544629-146-1.aspx

    http://www.sqlservercentral.com/Forums/FindPost1558835.aspx

    this may be what you are looking for....but please read the comments from Jeff Moden after my post.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • April 21, 2014 at 5:33 am

    #1707408

    Please choose the CDC for selective column audit logs, rather than writing procedure and killing your performance 🙂

  • April 21, 2014 at 8:37 am

    #1707463

    Saravanan_tvr (4/21/2014)

    Please choose the CDC for selective column audit logs, rather than writing procedure and killing your performance 🙂

    I don't know where people come up with such a notion. Writing an audit trigger for this isn't going to kill performance if done properly. To be honest and from what I've read about it so far, I'm not impressed with CDC.

    --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)

  • April 21, 2014 at 9:12 am

    #1707477

    CDC is an enterprise only feature as well. That can be an issue.

    The CDC stuff can get complex and be cumbersome to deal with, especially with DR environments. Make sure you practice working with it. It can also potentially be a performance issue, as Jeff noted.

    If you are looking to write audit triggers, you can selectively check which items were changed with the UPDATE() function or use a CASE to do comparisons between inserted and deleted and then insert a value. I might do the latter, having SQL do comparisons and insert null for those columns not changed.

  • April 22, 2014 at 2:45 pm

    #1707917

    Jeff Moden (4/21/2014)

    I don't know where people come up with such a notion. Writing an audit trigger for this isn't going to kill performance if done properly. To be honest and from what I've read about it so far, I'm not impressed with CDC.

    Yes, if all you want to do is auditing, using CDC is like driving a screw with a sledgehammer. Not only is it overly powerful - it can't do the job properly. To wit, CDC can't tell you who did the change, which is quite important when it comes to auditing.

    For the actual posting at hand, I would ignore the problem, unless you know that the columns are always updated in fixed sets of 10 (or whatever).

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • April 23, 2014 at 7:05 am

    #1708076

    Thanks Jeff,

  • April 23, 2014 at 7:30 am

    #1708094

    Steve Jones - SSC Editor (4/21/2014)

    CDC is an enterprise only feature as well. That can be an issue.

    The CDC stuff can get complex and be cumbersome to deal with, especially with DR environments. Make sure you practice working with it. It can also potentially be a performance issue, as Jeff noted.

    If you are looking to write audit triggers, you can selectively check which items were changed with the UPDATE() function or use a CASE to do comparisons between inserted and deleted and then insert a value. I might do the latter, having SQL do comparisons and insert null for those columns not changed.

    Do be careful inserting NULL into an audit table for columns that don't change. I did this exact scenario at one point and it really bit me hard. It was impossible to tell when a value was set to NULL that previously had a value. :w00t:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 8 posts - 1 through 7 (of 7 total)

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