I am running MySQL v8 with LibreOffice Base as the front end. I have the following table trigger defined:
DELIMITER $$
CREATE TRIGGER bol.get_commodity_number
BEFORE UPDATE ON
bol.packinglistdetail FOR EACH ROW
BEGIN
SET NEW.CommodityNumber = (SELECT D.CommodityNumber
FROM packinglistdetail P INNER JOIN
description_picklist D
ON P.Commodity = D.Commodity
WHERE P.CommodityNumber IS NULL );
END$$
It correctly assigns the commodity number from the subquery upon the first save of the table. However, if you go back and update any row on that table, the CommodityNumber gets set to NULL. Then if you edit any row again, it correctly gets the commodity number from the subquery. It alternates back and forth from the correct to the NULL value. Any help would be appreciated.