title: Data logging: Triggers or special design pattern?
body: <p>I'm currently working on a logging solution for one of our web projects.</p>
  
  <ul>
  <li>Server side language: PHP 5.2.3</li>
  <li>Database: MySQL 5.0</li>
  <li>Apache 2</li>
  </ul>
  
  <p>Similar as described in here </p>
  
  <p><a href="https://stackoverflow.com/questions/870142/mysql-trigger-based-audit-logging-with-comparisons">MySQL Trigger based Audit logging with comparisons</a></p>
  
  <p>we want to log changed columns for existing records, insertion of new records and deletion of records. For updates I have to somehow compare the old values
  of the DB records with the the ones, the user saved. For insert the logging is easy and for delete I have to get the old values first to store then before deletion in the logging table.</p>
  
  <p>The question I'm facing right now is. What do you think is the better solution.</p>
  
  <p>Logging via Triggers on the DB level or using a suitable design pattern on the application level.</p>
  
  <p>There already exists some threads about it, but there is no consideration what is better, one (Triggers) or the other (on application level).</p>
  
  <p>In another thread AOP is suggested</p>
  
  <p><a href="https://stackoverflow.com/questions/1313364/which-design-pattern-would-you-consider-when-logging-is-needed">Which design pattern would you consider when Logging is needed?</a></p>
  
  <p>I'm going with the DB design for logging #1 as suggested in here</p>
  
  <p><a href="https://stackoverflow.com/questions/617225/audit-logging-strategies">Audit Logging Strategies</a></p>
  
  <p>where I have a separate table for logging for each observed table. The structure is like the following</p>
  
  <pre><code>ID : Integer
  COLUMN: String, the column name of the column that changed
  OLD_VAL: String
  NEW_VAL: String
  SOURCE: String, the source which changed the data, currently user and an agent are possible as sources
  create_at: DATETIME, then the change occurred
  </code></pre>
  
  <p>So what do you people think. Considering performance vs. clean code and information hiding, what is the better solution to achieve a logging system like this.</p>
  
  <p>Any ideas appreciated.</p>