Audit two different tables in SQL Server

Question

Hi All,

I have a form with data. Any changes or insertion , those data should be updated in tow different tables like name, salary in one table and address, mail id in another table.

Like the example above i have several columns in both tables.

Now i want to audit the table. So i think i have to create a view for the two tables and set up a trigger for the view. Is it correct?.

And also i need to know only the affected columns. How to get the only affected columns?

Please suggest me a solution.

Thanks!!

Answer 1

There are lots of ways to let the system handle all that grunt work for you - depending on the SQL Server version you're using:

• How to: Use SQL Server Change Tracking (as of SQL Server 2008)

• Introduction to SQL Server change tracking

• Understanding SQL Server Audit (as of SQL Server 2008 R2)

• Articles for SQL Server Auditing (various versions)

If you really must handle all the work yourself, you need to get familiarized with triggers - read up on them in Data Points: Exploring SQL Server Triggers.

Inside your trigger code, you have two "pseudo-tables":

• Inserted is the table holding the values being inserted (in an INSERT trigger) or the new values (in an UPDATE trigger)
• Deleted is the table holding the values being deleted (in a DELETE trigger) or the old values (in an UPDATE trigger)

With those two pseudo-tables, you can get access to all data you might need.