C# update audit table

Question

In a C# 2010 desktop application I need to add an audit table to an application that would be similar to the 'main' table. Basically whenever anyting is changed in the 'main' table I need to show the corresponding change in the 'audit' table. The 'audit' table will be a mirror copy of the 'main' table. The only difference is one table is called 'main' table and the other table is called the 'audit' table. **Note these table reside in a sql server 2008 r2 database. Would you setup a trigger? If so can you show me the sql you would use? Would you write C#.net 2010 code? If so can you show me the code that would solve my problem?

Jignesh Trivedi · Answer

hi A SQL trigger is a set of SQL statements stored in the database catalog. A SQL trigger is executed or fired whenever an event that is associated with a table occurs e.g. insert update or delete.A SQL trigger is also known as a special type of stored procedure because it is not called directly by users like a stored procedure.The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table while a stored procedure must be called explicitly. please refer below link it help you to understand triggger and how to set up it. http://msdn.microsoft.com/en-us/library/ms189799.aspx change data capture is capture insert update and delete activity on SQL tables.CDC contains a column structure that is exactly the same as the column structure of the source table. CDC has also some clean up time. before clen up time you can run you sql Job that transfer you audit log data to other safe table. http://www.c-sharpcorner.com/UploadFile/ff2f08/change-data-capture-cdc-in-sql-server-2008/ hope this will help you.

dc · Answer

I have the following additional questions 1. If I chose to use a trigger where is the sql stored and how does it get executed? For example if I setup a stored procedure in sql seerver managment studio 2008 r2 I select programmability and select stored procedure. I then create the stored procedure. I then have a job that executes the stored procedure. Can you tell me how this would be setup for a database trigger? 2. If I cose the CDC (Change Data Capture) and From SQL Job can you show me more code on how to accomplish this goal?

Jignesh Trivedi · Answer

hi for audit log you may use trigger. Approach is same as mention in below link http://www.codeproject.com/Articles/25600/Triggers-Sql-Server But when you trigger there is synchronous call. So performance of DML is degrading. Instead of trigger you can use CDC (Change Data Capture) and From SQL Job you can move your data from CDC table to Audit log table. http://www.c-sharpcorner.com/UploadFile/ff2f08/change-data-capture-cdc-in-sql-server-2008/ You may also use Service broker to make asynchronous call. hope this will help you.