Tracking Data Changes

Question

Tracking Data Changes

Viewing 10 posts - 16 through 24 (of 24 total)

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

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

Yeah you are going to have to maintain your audit table and your trigger as you make changes to the base table. This is pretty much unavoidable if you need to capture the new column(s).

_______________________________________________________________

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 2

Hi Sean Lange,

I had alter this sp's. When we added or remove columns in master tables it will automatically

altered in archive / history tables and also alter the triggers in master tables

Regards

Guru

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

If you are willing to share what you did I would be interested to see it.

_______________________________________________________________

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 4

sure but now i came to my room. I will post it tomorrow, code is in office

Regards

Guru

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 5

Hi Sean Lange,

Sorry for the delay.

I had attached 3 documents

CreateTriggerAfterAlterTable.txt

Is for altering the trigger when a column is added or deleted

And this will be called in

sp_StandardCDC_enable_db.txt

The below code will be add or delete column in created archive tables.

This code will be in sp_StandardCDC_enable_db.txt

select @SQLcmd=CONVERT(sysname, EventData().query(' + QUOTENAME('data(//TSQLCommand)', '''') + '))

set @SQLcmd=Replace(@SQLcmd,PARSENAME(REPLACE(@SQLcmd, ''table '' , ''.''), 2),'' History'');

exec (@SQLcmd)

select Replace(@SQLcmd,PARSENAME(REPLACE(@SQLcmd, ''table '' , ''.''), 2),'' History'');

Regards

Guru

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

No problem. Thanks for sharing. These should prove to be an interesting read.

_______________________________________________________________

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,

Let me know if there is any change / modification regarding performance (writing simple query)

Regards

Giri

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 8

Without disabling triggers how can be prevent trigger getting fired.. Because if we use multi-user it will be problem when disabling trigger right?

Regards

Guru

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 9

Hi all,

Can we use SET CONTEXT_INFO 0x9999 to disable trigger in current session /user?

Regards

Guru

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

You could do that however I would argue that writing your trigger to sidestep auditing is not really auditing. Depending on the business this kind of loophole can be big problems. Things like Haliburton come to mind. If your auditing data is not that serious then this type of work around should work.

_______________________________________________________________

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/