title: SQL Trigger to record UPDATE into Audit Table
body: <p>I have a table with columns <code>businessname</code>, <code>sortcode</code> and <code>accountnumber</code> all populated, <code>name</code>, <code>nationality</code> and <code>DOB</code> all currently unpopulated. I need to create a trigger to shoot every update to an audit table when any of the null fields are updated so if I change just the name I'll get a timestamp, userid, the field changed, the old value and the new value.. If I changed all 3 null fields I'd like to send 3 rows to the audit table.</p>
  
  <p>Can someone give me a pointer on the logic of this please? </p>
  
  <p>In a very rudimentary format for testing I've got</p>
  
  <pre><code>CREATE TRIGGER RM_UPDATE_TRIGGER ON RM_BASE
  ON UPDATE 
  AS 
      INSERT INTO RM_AUDITLOG
         SELECT CURRENT_TIMESTAMP, SRT_CD
         FROM RM_BASE
  </code></pre>
  
  <p>but this is sending all the current rows across after an UPDATE to any of them, I only want the row that has been affected. I'm not sure if I should be building more tables to join together to get the final answer or using the INSERT/DELETE tables.. I've seen an audit table in this format in previous roles so I know it works but can't figure it out! </p>
  
  <p>Thanks</p>