Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jassie
Constraint Violating Yak Guru
332 Posts |
Posted - 2013-01-22 : 22:00:00
|
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?What sql would you use? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
jassie
Constraint Violating Yak Guru
332 Posts |
Posted - 2013-01-23 : 10:55:10
|
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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-23 : 13:10:20
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
jassie
Constraint Violating Yak Guru
332 Posts |
Posted - 2013-01-27 : 20:18:55
|
Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-27 : 23:59:21
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2013-01-28 : 13:46:05
|
If I amy add my two cents worth here: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.Here is a nice article on the subject:[url]http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/[/url]Zath |
|
|
|
|
|