MySQL不像PostGres那样有RETURN TRIGGER。使用一个普通的存储过程，并将动物ID作为参数传递。
获取动物名称的SELECT子查询无法工作，因为触发器在该行被删除 * 之后运行。因此将OLD.animal_name作为参数传递。

DELIMITER $$

CREATE PROCEDURE animal_delete_func(old_animal_id, old_animal_name)
BEGIN 
    -- Delete the feeding schedule entries for the animal

    DELETE FROM feeding_schedule WHERE animal_id = old_animal_id;

    -- Delete the food entries for the animal

    DELETE FROM food WHERE animal_id = old_animal_id;

    -- INSERT into deleted_animals table

    INSERT INTO deleted_animals (animal_id, animal_name, date_deleted)
    VALUES (old_animal_id, old_animal_name, NOW());
 
 RETURN OLD

END$$

-- Set delimiter back to ;
DELIMITER ;

-- trigger that calls animal_delete_func() everytime an animal is deleted from the animals table

CREATE TRIGGER animal_delete_trigger
AFTER DELETE ON animals
FOR EACH ROW
CALL animal_delete_func(OLD.animal_id, OLD.animal_name);

您正在使用存储过程语法而不是触发器语法：试试这个：

DELIMITER $$

CREATE TRIGGER animal_delete_trigger
BEFORE DELETE ON animals
FOR EACH ROW
BEGIN
  -- Delete the feeding schedule entries for the animal
  DELETE FROM feeding_schedule WHERE animal_id = OLD.animal_id;
  
  -- Delete the food entries for the animal
  DELETE FROM food WHERE animal_id = OLD.animal_id;
  
  -- INSERT into deleted_animals table
  INSERT INTO deleted_animals (animal_id, animal_name, date_deleted)
  VALUES (OLD.animal_id, OLD.animal_name, NOW());
  
END$$

DELIMITER ;