Skip to Main Content

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
209 views