MySQL Trigger AFTER UPDATE IF ELSE insert

Question

I'm trying to make a trigger insert into a table after the update of the checkedOutBy in the equip table.

It gave me an Error 1064 at lines 7, 10 and others as I have randomly changed things hoping that somehow it may just magically work.

DELIMITER $$

CREATE TRIGGER moveToHistory AFTER UPDATE ON equip
FOR EACH ROW
BEGIN

IF NEW.checkedOutBy <> Old.checkedOutBy
BEGIN

IF OLD.checkedOutBy IS NOT NULL AND NEW.checkedOutBy IS NULL THEN
     INSERT INTO equipmentHistory(assetId, operation, createdBy, creationDate)
     VALUES(new.assetId, 'CheckIn', eid, Date(sysdate()))
     NEW.status = 'CheckedIn';
ELSE IF OLD.checkedOutBy IS NULL AND NEW.checkedOutBy IS NOT NULL THEN
     INSERT INTO equipmentHistory(assetId, operation, createdBy, creationDate)
     VALUES(new.assetId, 'Checkout', eid, Date(sysdate()))
     NEW.status = 'CheckedOut';
END IF
END
END$$

DELIMITER ;

Answer 1

Here is the trigger with proper syntax for if - elseif

DELIMITER $$
CREATE TRIGGER moveToHistory AFTER UPDATE ON equip
FOR EACH ROW
BEGIN
IF NEW.checkedOutBy <> Old.checkedOutBy then
   IF OLD.checkedOutBy IS NOT NULL AND NEW.checkedOutBy IS NULL THEN
      INSERT INTO equipmentHistory(assetId, operation, createdBy, creationDate)
      VALUES(new.assetId, 'CheckIn', eid, curdate());
   ELSEIF OLD.checkedOutBy IS NULL AND NEW.checkedOutBy IS NOT NULL THEN
      INSERT INTO equipmentHistory(assetId, operation, createdBy, creationDate)
       VALUES(new.assetId, 'Checkout', eid, curdate());
   END IF;
END IF ;
END;$$

DELIMITER ;

Also note that I have removed NEW.status = 'CheckedIn'; and NEW.status = 'CheckedOut'; , these does not make sense when you try to set using a after update trigger