How to log data changed in sql server tables. Which approach is better

Just wanted to know about logging data modification.

I have seen to ways of tracking data change (DML).

1. Using Triggers
2. Keeping columns in same table for Added Date, Added By, Modifie开发者_JAVA技巧d Date, Modified By.

Using approach (1), I can write triggers for Insert/Delete/Update on each table to log changes and hence can apply foreign key relationship and other constraints like unique key constraints on all the tables as per requirement.

But I didn't understand how it is possible to apply various constrains using approach (2). Since I have to make composite unique key and have to consider many more columns.

Is there any design issues in database tables. What is the suggested way for approach (2) to log data.

Which approach is better.

Also I have come to know from some of my colleagues that triggers do no fire on bulk insert queries is it true?

The solution I like best for this is to require (using security features, if possible) all application data access (or at least all CRUD operations) to go through stored procedures, and to have the SPs manage change tracking and auditing.

The problem with triggers is that they don't scale well; they can impose significant performance hits by turning what could be batched inserts or updates into many single-row operations.

As to whether to keep the changes in-row or in another table, my preference is to use another table. That way, I can also have a history of actual changes when needed, rather than just who-and-when. Keeping the source table narrow can also help with query/read performance.

You can also apply partitioning to the audit tables, which will make them easy to age off / drop when the time comes.

You're missing a big one: Change Data Capture. Sql Server Enterprise Edition has this feature built in already.