Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two Tables:

Articles(artID, artContents, artPublishDate, artCategoryID, publisherID).

ArticleUpdated(upArtID, upArtContents, upArtEditedData, upArtPublishDate, upArtCategory, upArtOriginalArticleID, upPublisherID)

A user logs in to the application and updates an article's contents in the artContents column. I want to:

  1. Know which Changes the user made to the article's contents?

  2. Store both versions of the Article, the Original version and the Edited Version!

What should I do for the above two task:

  1. Are there any necessary changes in the tables?

  2. What is the query for getting exact edited data of the artContents column?

    • (The exact edited data means that there may be 5000 characters in the columns. The user may edit 200 characters in the middle or somewhere else in column's characters. I want the exact edited characters, before the edit and after the edit.)

Note: I am using ASP.NET with C# for Developing

share|improve this question

2 Answers 2

There's a solution for SQL Server. You can create triggers for tracking these changes, tables where the old and new values will be stored and when a trigger is fired, insert the old and new values into the storage tables

You can also use a tool that will track the changes, I use ApexSQL Audit - doesn't actually store both versions of the complete table, but stores old and new values for the modified column, so you can recreate the old versions. As it stores all changes in database tables, so I've created some custom reports

And to answer your questions directly

Are there any necessary changes in the tables?

Yes, triggers are added

What is the query for getting exact edited data of the artContents column?

It doesn't show just the modified characters, but the old and the new column values

share|improve this answer

You can use a built in feature called Change Data capture.

It may not cover everything you want, but it will do your historical tracking. You'll need to use triggers or something else to track the query

Change Data Capture

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.