CREATE TRIGGER `partsonorder_after_upd_tr` AFTER UPDATE ON `partsonorder`
  FOR EACH ROW
BEGIN
  INSERT INTO
    `job_audits`
  SET
    `job_audits`.`job_no` = NEW.`JOB_NO`,
    `job_audits`.`system` = @system_name,
    `job_audits`.`created_at` = NOW(),
    `job_audits`.`code` = 6,
    `job_audits`.`notes` = CONCAT_WS(
      ', ',
      CONCAT('Edited part on order ', NEW.`id`),
      IF(NEW.`ORDERDATE` <=> OLD.`ORDERDATE`, NULL, CONCAT('Ordered: ', QUOTE(OLD.`ORDERDATE`), ' => ', QUOTE(NEW.`ORDERDATE`))),
      IF(NEW.`ORDERNO` <=> OLD.`ORDERNO`, NULL, CONCAT('Order No: ', QUOTE(OLD.`ORDERNO`), ' => ', QUOTE(NEW.`ORDERNO`))),
      IF(NEW.`supplier_id` <=> OLD.`supplier_id`, NULL, CONCAT('Supplier: ', QUOTE(OLD.`supplier_id`), ' => ', QUOTE(NEW.`supplier_id`))),
      IF(NEW.`supplier_id` <=> OLD.`supplier_id`, NULL, CONCAT('Supplier name: ', (SELECT `name` FROM `suppliers` s WHERE s.`id` = NEW.`supplier_id`))),
      IF(NEW.`supplier_ref` <=> OLD.`supplier_ref`, NULL, CONCAT('Supplier Ref: ', QUOTE(OLD.`supplier_ref`), ' => ', QUOTE(NEW.`supplier_ref`))),
      IF(NEW.`PART_CODE` <=> OLD.`PART_CODE`, NULL, CONCAT('Part Code: ', QUOTE(OLD.`PART_CODE`), ' => ', QUOTE(NEW.`PART_CODE`))),
      IF(NEW.`DESCRIPTION` <=> OLD.`DESCRIPTION`, NULL, CONCAT('Desc: ', QUOTE(OLD.`DESCRIPTION`), ' => ', QUOTE(NEW.`DESCRIPTION`))),
      IF(NEW.`QUANTITY` <=> OLD.`QUANTITY`, NULL, CONCAT('Quantity: ', QUOTE(OLD.`QUANTITY`), ' => ', QUOTE(NEW.`QUANTITY`))),
      IF(NEW.`COST` <=> OLD.`COST`, NULL, CONCAT('Cost: ', QUOTE(OLD.`COST`), ' => ', QUOTE(NEW.`COST`))),
      IF(NEW.`STATUS_ID` <=> OLD.`STATUS_ID`, NULL, CONCAT('Status: ', QUOTE(OLD.`STATUS_ID`), ' => ', QUOTE(NEW.`STATUS_ID`))),
      IF(NEW.`STATUS_ID` <=> OLD.`STATUS_ID`, NULL, CONCAT('Status Text: ', (SELECT ps.`description` FROM `partstatuses` ps WHERE ps.`id` = NEW.`STATUS_ID`))),
      IF(NEW.`ETADATE` <=> OLD.`ETADATE`, NULL, CONCAT('ETA: ', QUOTE(OLD.`ETADATE`), ' => ', QUOTE(NEW.`ETADATE`))),
      IF(NEW.`AVAILABLEDATE` <=> OLD.`AVAILABLEDATE`, NULL, CONCAT('Available: ', QUOTE(OLD.`AVAILABLEDATE`), ' => ', QUOTE(NEW.`AVAILABLEDATE`))),
      IF(NEW.`ENG_NUM` <=> OLD.`ENG_NUM`, NULL, CONCAT('Eng: ', QUOTE(OLD.`ENG_NUM`), ' => ', QUOTE(NEW.`ENG_NUM`))),
      IF(NEW.`ENG_NUM` <=> OLD.`ENG_NUM`, NULL, CONCAT('Eng Name: ', (SELECT e.`NAME` FROM `engnames` e WHERE e.`ENG_NUM` = NEW.`ENG_NUM`))),
      IF(NEW.`creditnote` <=> OLD.`creditnote`, NULL, CONCAT('Credit Note: ', QUOTE(OLD.`creditnote`), ' => ', QUOTE(NEW.`creditnote`))),
      CONCAT('(', @user_name, ')')
    );
END;