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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 using a trigger

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

Posted - 2013-01-23 : 00:58:39
its as simple as
insert into audit select column1,column2,.. from main

have a look at CREATE TRIGGER statement for syntax

http://msdn.microsoft.com/en-us/library/ms189799.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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?
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2013-01-27 : 20:18:55
Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-27 : 23:59:21
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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

Go to Top of Page
   

- Advertisement -