<p>What is the best way to track changes in a database table?</p>

<p>Imagine you got an application in which users (in the context of the application not DB users ) are able to change data which are store in some database table. What's the best way to track a history of all changes, so that you can show which user at what time change which data how?</p>

[{'answer_id': 38449, 'author': 'thelsdj', 'author_id': 163, 'author_profile': 'https://Stackoverflow.com/users/163', 'pm_score': 3, 'selected': False, 'text': "<p>You've got a few issues here that don't relate well to each other.</p>\n\n<p>At the basic database level you can track changes by having a separate table that gets an entry added to it via triggers on INSERT/UPDATE/DELETE statements. Thats the general way of tracking changes to a database table.</p>\n\n<p>The other thing you want is to know which <em>user</em> made the change. Generally your triggers wouldn't know this. I'm assuming that if you want to know which user changed a piece of data then its possible that multiple users could change the same data.</p>\n\n<p>There is no right way to do this, you'll probably want to have a separate table that your application code will insert a record into whenever a user updates some data in the other table, including user, timestamp and id of the changed record.</p>\n\n<p>Make sure to use a transaction so you don't end up with cases where update gets done without the insert, or if you do the opposite order you don't end up with insert without the update.</p>\n"}, {'answer_id': 38451, 'author': 'Unsliced', 'author_id': 2902, 'author_profile': 'https://Stackoverflow.com/users/2902', 'pm_score': 1, 'selected': False, 'text': '<p>A trace log in a separate table (with an ID column, possibly with timestamps)? </p>\n\n<p>Are you going to want to undo the changes as well - perhaps pre-create the undo statement (a DELETE for every INSERT, an (un-) UPDATE for every normal UPDATE) and save that in the trace? </p>\n'}, {'answer_id': 38453, 'author': 'Jeremy McGee', 'author_id': 3546, 'author_profile': 'https://Stackoverflow.com/users/3546', 'pm_score': 5, 'selected': True, 'text': '<p>In general, if your application is structured into layers, have the data access tier call a stored procedure on your database server to write a log of the database changes.</p>\n\n<p>In languages that support such a thing <a href="http://en.wikipedia.org/wiki/Aspect-oriented_programming" rel="noreferrer">aspect-oriented programming</a> can be a good technique to use for this kind of application. Auditing database table changes is the kind of operation that you\'ll typically want to log for all operations, so AOP can work very nicely.</p>\n\n<p>Bear in mind that logging database changes will create lots of data and will slow the system down. It may be sensible to use a message-queue solution and a separate database to perform the audit log, depending on the size of the application.</p>\n\n<p>It\'s also perfectly feasible to use stored procedures to handle this, although there may be a bit of work involved passing user credentials through to the database itself.</p>\n'}, {'answer_id': 38458, 'author': 'Kevin Fairchild', 'author_id': 3743, 'author_profile': 'https://Stackoverflow.com/users/3743', 'pm_score': 3, 'selected': False, 'text': '<p>One method I\'ve seen quite often is to have audit tables. Then you can show just what\'s changed, what\'s changed and what it changed from, or whatever you heart desires :)  Then you could write up a trigger to do the actual logging.  Not too painful if done properly...</p>\n\n<p>No matter how you do it, though, it kind of depends on how your users connect to the database.  Are they using a single application user via a security context within the app, are they connecting using their own accounts on the domain, or does the app just have everyone connecting with a generic sql-account?</p>\n\n<p>If you aren\'t able to get the user info from the database connection, it\'s a little more of a pain.  And then you might look at doing the logging within the app, so if you have a process called "CreateOrder" or whatever, you can log to the Order_Audit table or whatever.</p>\n\n<p>Doing it all within the app opens yourself up a little more to changes made from outside of the app, but if you have multiple apps all using the same data and you just wanted to see what changes were made by yours, maybe that\'s what you wanted... &lt;<em>shrug</em>></p>\n\n<p>Good luck to you, though!</p>\n\n<p>--Kevin</p>\n'}, {'answer_id': 13782941, 'author': 'TomNysetvold', 'author_id': 1309534, 'author_profile': 'https://Stackoverflow.com/users/1309534', 'pm_score': 2, 'selected': False, 'text': '<p>In researching this same question, I found a discussion <a href="https://web.archive.org/web/20210506085627/http://www.4guysfromrolla.com/webtech/041807-1.shtml" rel="nofollow noreferrer">here</a> very useful. It suggests having a parallel table set for tracking changes, where each change-tracking table has the same columns as what it\'s tracking, plus columns for who changed it, when, and if it\'s been deleted. (It should be possible to generate the schema for this more-or-less automatically by using a regexed-up version of your pre-existing scripts.) </p>\n'}, {'answer_id': 38466935, 'author': 'Christian Del Bianco', 'author_id': 4391946, 'author_profile': 'https://Stackoverflow.com/users/4391946', 'pm_score': 1, 'selected': False, 'text': '<p>Let\'s try with this open source component:</p>\n\n<p><a href="https://tabledependency.codeplex.com/" rel="nofollow">https://tabledependency.codeplex.com/</a></p>\n\n<p>TableDependency is a generic C# component used to receive notifications when the content of a specified database table change. </p>\n'}, {'answer_id': 42050195, 'author': 'moledet', 'author_id': 3564896, 'author_profile': 'https://Stackoverflow.com/users/3564896', 'pm_score': 0, 'selected': False, 'text': '<p>If all changes from php. You may use <a href="https://github.com/moledet/database-change-log" rel="nofollow noreferrer">class</a> to log evry INSERT/UPDATE/DELETE before query. It will be save <strong><em>action</em></strong>, <strong><em>table</em></strong>, <strong><em>column</em></strong>, <strong><em>newValue</em></strong>, <strong><em>oldValue</em></strong>, <strong><em>date</em></strong>, <strong><em>system</em></strong>(if need), <strong><em>ip</em></strong>, <strong><em>UserAgent</em></strong>, <strong><em>clumnReference</em></strong>, <strong><em>operatorReference</em></strong>, <strong><em>valueReference</em></strong>. All tables/columns/actions that need to log are configurable. </p>\n'}, {'answer_id': 44182789, 'author': "S'chn T'gai Spock", 'author_id': 4767356, 'author_profile': 'https://Stackoverflow.com/users/4767356', 'pm_score': 2, 'selected': False, 'text': '<p>Suppose I have a Person Table with 10 columns which include PersonSid and UpdateDate. Now, I want to keep track of any updates in Person Table.\nHere is the simple technique I used:</p>\n\n<ol>\n<li><p>Create a person_log table</p>\n\n<p>create table person_log(date datetime2, sid int);</p></li>\n<li><p>Create a trigger on Person table that will insert a row into person_log table whenever Person table gets updated:</p>\n\n<p>create trigger tr on dbo.Person<br>\nfor update<br>\nas \ninsert into person_log(date, sid) select updatedDTTM, PersonSID from inserted</p></li>\n</ol>\n\n<p>After any updates, query person_log table and you will be able to see personSid that got updated.\nSame you can do for Insert, delete.</p>\n\n<p>Above example is for SQL, let me know in case of any queries or use this link :\n<a href="https://web.archive.org/web/20211020134839/https://www.4guysfromrolla.com/webtech/042507-1.shtml" rel="nofollow noreferrer">https://web.archive.org/web/20211020134839/https://www.4guysfromrolla.com/webtech/042507-1.shtml</a> </p>\n'}]
['https://Stackoverflow.com/questions/38437', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3711/']