title: How to log data changed in sql server tables. Which approach is better
body: <p>Just wanted to know about logging data modification.</p>
  
  <p>I have seen to ways of tracking data change (DML). </p>
  
  <ol>
  <li>Using Triggers</li>
  <li>Keeping columns in same table for Added Date, Added By, Modified Date, Modified By.</li>
  </ol>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Is there any design issues in database tables. What is the suggested way for approach (2) to log data.</p>
  
  <p>Which approach is better.</p>
  
  <p>Also I have come to know from some of my colleagues that triggers do no fire on bulk insert queries is it true?</p>