Microsoft SQL triggers on columns

Question

Post reply

Microsoft SQL triggers on columns

anandkaria

Grasshopper

Points: 17
More actions
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.

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

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

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

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

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)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 4

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.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

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]

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 6

Saravanan_tvr

SSCarpal Tunnel

Points: 4558

April 23, 2014 at 7:05 am

#1708076

Thanks Jeff,

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

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/