Skip to Main Content

Table trigger updates correctly and then back to NULL

MartyBMay 18 2020 — edited May 20 2020

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.

Comments
Post Details
Added on May 18 2020
3 comments
26 views