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?
Can you explain to me how the trigger is executed? Also can you tell me if you would add any extra columns to the trigger like, what type of a change is made, who made the change?
trigger will be fired automatically each time you do the DML action(INSERT/UPDATE/DELETE) You can add extra columns inside if you want. Type of change can be determined based on internal temporary tables DELETED and INSERTED.
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
When using a trigger and in any stored procedure that uses that table with a trigger attached, if you get any identity from the table from an insert, be sure to use SCOPE_IDENTITY().
Do not use @@Identity as this will get the identity of your history or archive table that the trigger uses.
using a trigger
Posted - 01/22/2013 : 22:00:00
