Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Microsoft SQL triggers on columns Expand / Collapse
Author
Message
Posted Sunday, April 20, 2014 3:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 6:16 AM
Points: 1, Visits: 1
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.
Post #1563319
Posted Sunday, April 20, 2014 4:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:06 AM
Points: 1,917, Visits: 19,596
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
Post #1563321
Posted Monday, April 21, 2014 5:33 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:30 AM
Points: 280, Visits: 1,218
Please choose the CDC for selective column audit logs, rather than writing procedure and killing your performance

Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
Post #1563439
Posted Monday, April 21, 2014 8:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:01 PM
Points: 35,372, Visits: 31,923
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563500
Posted Monday, April 21, 2014 9:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:00 PM
Points: 31,181, Visits: 15,627
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1563516
Posted Tuesday, April 22, 2014 2:45 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:20 PM
Points: 807, Visits: 725
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).


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1564041
Posted Wednesday, April 23, 2014 7:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:30 AM
Points: 280, Visits: 1,218
Thanks Jeff,

Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
Post #1564240
Posted Wednesday, April 23, 2014 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1564261
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse