title: Using IF statements in a MySQL trigger to insert multiple rows
body: <p>I have a trigger that stores changes made in a separate table when a user edits the data. This data is written out on a web page beneath the current data in human readable format, i.e. </p>
  
  <p>23/04/09 22:47 James Smith changed Tenant Name from "George Hill" to "George Hilling".</p>
  
  <p>The trigger I have looks a bit like this - (this is the abridged version). </p>
  
  <p>Two questions:</p>
  
  <p>A) Is this quite costly performance-wise and if so is there a better approach to take?
  B) Is there a tidier way to do this without all the IFs, using some sort of loop perhaps?</p>
  
  <pre><code>DELIMITER //
  CREATE TRIGGER repair_history AFTER UPDATE ON repairs
  FOR EACH ROW
  BEGIN
  INSERT INTO repair_edit SET repair_id=NEW.repair_id,
  edit_date_time=NEW.edit_date_time, edited_by=NEW.edited_by;
  
  IF OLD.tenant_name != NEW.tenant_name THEN
  INSERT INTO repair_history SET edit_id=LAST_INSERT_ID(), field='tenant_name',
  former_field_value=OLD.tenant_name, new_field_value=NEW.tenant_name;
  END IF;
  
  IF OLD.priority != NEW.priority THEN
  INSERT INTO repair_history SET edit_id=LAST_INSERT_ID(), field='priority',
  former_field_value=OLD.priority, new_field_value=NEW.priority;
  END IF;
  
  IF OLD.property_id != NEW.property_id THEN
  INSERT INTO repair_history SET edit_id=LAST_INSERT_ID(), field='property_id',
  former_field_value=OLD.property_id, new_field_value=NEW.property_id;
  END IF;
  
  IF OLD.type_id != NEW.type_id THEN
  INSERT INTO repair_history SET edit_id=LAST_INSERT_ID(), field='type_id',
  former_field_value=OLD.type_id, new_field_value=NEW.type_id;
  END IF;
  
  END; //
  DELIMITER ;
  </code></pre>