Keep history table [modified]
-
Hello, I need to insert the newly inserted/Updated/Deleted records into History table in following format. ID - Field Name - Old Value - New Value - Date So what would be the best way to achive this. Should i go for Trigger? Write insert statement in each insert/Update/Delete stored procedure? Demo code would help more. As described above, There would be a table names "History" having columns, ID - TableName - Field Name - Old Value - New Value - Date - AdminID [Id of user who is modifing field] Now Table name and field name would be the values indicating the inserted/modified/delete field name. Oldvalue coloumn would be showing old value if any and new value would show current value. So all the database table would have single History table. Hope this made more clear. Thanks. Thanks in advance.
modified on Thursday, June 16, 2011 12:46 AM
-
Hello, I need to insert the newly inserted/Updated/Deleted records into History table in following format. ID - Field Name - Old Value - New Value - Date So what would be the best way to achive this. Should i go for Trigger? Write insert statement in each insert/Update/Delete stored procedure? Demo code would help more. As described above, There would be a table names "History" having columns, ID - TableName - Field Name - Old Value - New Value - Date - AdminID [Id of user who is modifing field] Now Table name and field name would be the values indicating the inserted/modified/delete field name. Oldvalue coloumn would be showing old value if any and new value would show current value. So all the database table would have single History table. Hope this made more clear. Thanks. Thanks in advance.
modified on Thursday, June 16, 2011 12:46 AM
It all depends on what database you are using. You can use a trigger, or you can simply update your code to call another stored procedure to put an entry into the History table. Clearly if you are deleting a record, you will need to make a copy of it before you actually delete it!! The power is in your hands!!
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
Hello, I need to insert the newly inserted/Updated/Deleted records into History table in following format. ID - Field Name - Old Value - New Value - Date So what would be the best way to achive this. Should i go for Trigger? Write insert statement in each insert/Update/Delete stored procedure? Demo code would help more. As described above, There would be a table names "History" having columns, ID - TableName - Field Name - Old Value - New Value - Date - AdminID [Id of user who is modifing field] Now Table name and field name would be the values indicating the inserted/modified/delete field name. Oldvalue coloumn would be showing old value if any and new value would show current value. So all the database table would have single History table. Hope this made more clear. Thanks. Thanks in advance.
modified on Thursday, June 16, 2011 12:46 AM
What are the business requirements? Will end-users see/use the data, a silent audit log,.. ? What will it be used for? Brad
-
Hello, I need to insert the newly inserted/Updated/Deleted records into History table in following format. ID - Field Name - Old Value - New Value - Date So what would be the best way to achive this. Should i go for Trigger? Write insert statement in each insert/Update/Delete stored procedure? Demo code would help more. As described above, There would be a table names "History" having columns, ID - TableName - Field Name - Old Value - New Value - Date - AdminID [Id of user who is modifing field] Now Table name and field name would be the values indicating the inserted/modified/delete field name. Oldvalue coloumn would be showing old value if any and new value would show current value. So all the database table would have single History table. Hope this made more clear. Thanks. Thanks in advance.
modified on Thursday, June 16, 2011 12:46 AM
Depends on other information. Also you might want to verify if the user that originated the change must be tracked. Are you sure you want to keep a record by column versus just keeping the entire row? Your solution adds complexity and your actual use cases and retention might not make it worthwhile.
-
Hello, I need to insert the newly inserted/Updated/Deleted records into History table in following format. ID - Field Name - Old Value - New Value - Date So what would be the best way to achive this. Should i go for Trigger? Write insert statement in each insert/Update/Delete stored procedure? Demo code would help more. As described above, There would be a table names "History" having columns, ID - TableName - Field Name - Old Value - New Value - Date - AdminID [Id of user who is modifing field] Now Table name and field name would be the values indicating the inserted/modified/delete field name. Oldvalue coloumn would be showing old value if any and new value would show current value. So all the database table would have single History table. Hope this made more clear. Thanks. Thanks in advance.
modified on Thursday, June 16, 2011 12:46 AM
Make sure you build in a process for archiving this History table on a periodic basis. It will grow by leaps and bounds in no time. Also, consider a method for turning this auditing off ... there may be a case where you have a large update job that you don't really want to audit. Just a thought from someone who has been there ... :cool: Good luck.:thumbsup:
-
Hello, I need to insert the newly inserted/Updated/Deleted records into History table in following format. ID - Field Name - Old Value - New Value - Date So what would be the best way to achive this. Should i go for Trigger? Write insert statement in each insert/Update/Delete stored procedure? Demo code would help more. As described above, There would be a table names "History" having columns, ID - TableName - Field Name - Old Value - New Value - Date - AdminID [Id of user who is modifing field] Now Table name and field name would be the values indicating the inserted/modified/delete field name. Oldvalue coloumn would be showing old value if any and new value would show current value. So all the database table would have single History table. Hope this made more clear. Thanks. Thanks in advance.
modified on Thursday, June 16, 2011 12:46 AM
If your using SQL Server 2005 or greater have a look at the TSQL Output clause[^] this will give you access to the deleted and inserted tables that you normally get with a trigger.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch