title: Multiple triggers vs a single trigger
body: <p><strong>Scenario:</strong></p>
  
  <p>Each time data is inserted/updated/deleted into/in/from a table, up to 3 things need to happen:</p>
  
  <ol>
  <li>The data needs to be logged to a separate table</li>
  <li>Referencial integrity must be enforced on implicit related data (I'm referring to data that should be linked with a foreign key relationship, but isn't: eg. When a updating <code>Table1.Name</code> should also update <code>Table2.Name</code> to the same value)</li>
  <li>Arbitrary business logic needs to execute</li>
  </ol>
  
  <p>The architecture and schema of the database must not be changed and the requirements must be accomplished by using triggers.</p>
  
  <p><strong>Question</strong></p>
  
  <p>Which option is better?:</p>
  
  <ol>
  <li>A single trigger per operation (insert/update/delete) that handles multiple concerns (logs, enforces implicit referencial integrity, and executes arbitrary business logic).  This trigger could be named <code>D_TableName</code> ("D" for delete).</li>
  <li><p>Multiple triggers per operation that were segregated by concern.  They could be named:   </p>
  
  <ul>
  <li><code>D_TableName_Logging</code> - for logging when something is deleted from </li>
  <li><code>D_TableName_RI</code></li>
  <li><code>D_TableName_BL</code></li>
  </ul></li>
  </ol>
  
  <p>I prefer option 2 because a single unit of code has a single concern.  I am not a DBA, and know enough about SQL Server to make me dangerous.</p>
  
  <p>Are there any compelling reasons to handle all of the concerns in a single trigger?</p>