Tracking Data Changes

Question

Post reply

Tracking Data Changes

GuruGPrasad

SSCommitted

Points: 1768
More actions
March 22, 2012 at 6:30 am

#258109

Hi all ,
I'm working on inventory module. so that i need to create audit tables. whether i can use MS options for Tracking data change Insert/Update/Delete? Or need to create a separate tables for maintaining audit.
Thanks in advance
Regards
Guru.

Viewing 15 posts - 1 through 15 (of 24 total)

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

Abhilash T Mr or Mrs. 500 Points: 516 More actions · Answer 1

Abhilash T

Mr or Mrs. 500

Points: 516

March 22, 2012 at 7:49 am

#1462716

Use Triggers.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Or better than triggers look into Change Data Capture (CDC).

http://msdn.microsoft.com/en-us/library/bb522489.aspx

_______________________________________________________________

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/

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 3

Hi Sean Lange,

Thank you,

I tried for CDC but its working in enterprise edition only. I'm having standard edition

Regards

Guru

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Upgrade, or create custom audit solution based on triggers.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 5

GuruGPrasad

SSCommitted

Points: 1768

March 22, 2012 at 8:06 am

#1462730

Thank you Eugene Elutin

Regards

Guru

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

If you roll your own with triggers make sure you do some research about how to avoid the common mistake of assuming there is only 1 row being modified at a time. This is a very common, and potentially disastrous, error. Make sure your logic is set based so it can handle any number of rows being affected (and avoid cursors like the plague).

_______________________________________________________________

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/

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 7

Hi Sean Lange,

I think i can't use Triggers. Because, The values of Master table will be updated every time a record is inserted / updated / deleted in detail table.. So , i think I need to insert manual through SP's into audit table.

Thanks

Regards

Guru

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

GuruGPrasad (3/22/2012)
Hi Sean Lange,
I think i can't use Triggers. Because, The values of Master table will be updated every time a record is inserted / updated / deleted in detail table.. So , i think I need to insert manual through SP's into audit table.
Thanks
Regards
Guru

No, it should not stop you from using triggers. Just learn how it should be done properly. No read to variables from inserted/deleted...

If you want to guarantee that all changes are audited, you are better to use triggers, as it will not be possible to avoid audit until they are dropped.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Eugene Elutin (3/22/2012)
GuruGPrasad (3/22/2012)
Hi Sean Lange,
I think i can't use Triggers. Because, The values of Master table will be updated every time a record is inserted / updated / deleted in detail table.. So , i think I need to insert manual through SP's into audit table.
Thanks
Regards
Guru
No, it should not stop you from using triggers. Just learn how it should be done properly. No read to variables from inserted/deleted...
If you want to guarantee that all changes are audited, you are better to use triggers, as it will not be possible to avoid audit until they are dropped.

Not only that but manually inserting data through a SP is NOT auditing. The point of auditing is that it is done 100% of the time. Typically it is also best of it is silent. It is possible to disable triggers but for the most part you should be ok with that approach.

The values of Master table will be updated every time a record is inserted / updated / deleted in detail table.

Isn't this exactly what you are trying to capture??? It sounds like you want to audit the data which means you want to capture every single insert, update and delete right?

_______________________________________________________________

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/

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 10

Yes Sean Lange

Each and every action . Exactly what we get in CDC.

Delete Statement

Insert Statement

Value before Update Statement

Value after Update Statement

Regards

Guru

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

GuruGPrasad (3/22/2012)
Yes Sean Lange
Each and every action . Exactly what we get in CDC.
Delete Statement
Insert Statement
Value before Update Statement
Value after Update Statement
Regards
Guru

Right. And since CDC is not an option you will want triggers. Just make a copy of your table and append "_Audit" to the name. You will probably want to add a couple other columns like ChangeDate, ChangeUser. You really don't need to capture the after update values, your audit table will keep the history just fine. The value after update will be the next audit record or the current value if there are no more changes.

_______________________________________________________________

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/

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 12

Hi Eugene Elutin ,Sean Lange

I got one link that enables CDC in Standard edition. I need to try that too.

http://standardeditioncdc.codeplex.com/

Regards

Guru

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

GuruGPrasad (3/22/2012)
Hi Eugene Elutin ,Sean Lange
I got one link that enables CDC in Standard edition. I need to try that too.
http://standardeditioncdc.codeplex.com/
Regards
Guru

That looks interesting. If you try that please post back and let us know how that works!!

_______________________________________________________________

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/

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 14

Hi,Sean Lange

I gone through that Sp's. I had made some alteration in the Sp "sp_StandardCDC_enable_table". And its working fine. But Need some more alteration in that SP. If we add or remove columns in the master tables it should affect the triggers and archive tables.

Regards

Guru