MySQL error #1064 [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 9 years ago.

When trying to create the trigger below, I get this message

#1064 - You have an error in your S开发者_Go百科QL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; END' at line 9

Can someone please help? Thank you

delimiter $$

CREATE TRIGGER tr_update_item_status
AFTER UPDATE ON check_in 
FOR EACH ROW
BEGIN
  IF OLD.quantity > 0 AND NEW.quantity <= 0 THEN
    UPDATE check_in SET item_status = 'Sold';
  ELSEIF OLD.quantity <= 0 AND NEW.quantity > 0 THEN
    UPDATE check_in SET item_status = 'Available';
  ENDIF;
END;$$

delimiter ;

---

DELIMITER $$

CREATE TRIGGER tr_update_item_status
AFTER UPDATE ON check_in  
FOR EACH ROW
BEGIN
  IF OLD.quantity > 0 AND NEW.quantity <= 0 THEN
    UPDATE check_in SET item_status = 'Sold';
  ELSEIF OLD.quantity <= 0 AND NEW.quantity > 0 THEN
    UPDATE check_in SET item_status = 'Available';
  END IF;
END $$

DELIMITER ;

This should work. All the best.

---

DELIMITER $$

CREATE TRIGGER tr_update_item_status
BEFORE UPDATE ON check_in 
FOR EACH ROW
BEGIN
  IF OLD.quantity > 0 AND NEW.quantity <= 0 THEN
    SET NEW.item_status = 'Sold';
  ELSEIF OLD.quantity <= 0 AND NEW.quantity > 0 THEN
    SET NEW.item_status = 'Available';
  END IF;
END; $$

DELIMITER ;

Three(3) things:

• The ENDIF; should be END IF;
• This should be a BEFORE UPDATE trigger because data validation is better before any table operation
• The UPDATE statements are semantically wrong. I changed them to set the NEW column name instead