Skip to Main Content
Forums

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!

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

1. Are you running the query that uses the trigger in a transaction or have auto commit turned on?

2/ What is the setting for the consistency setting?

MartyB

I'll have to verify the Auto Commit flag and consistency settings.  I wouldn't know offhand how those are configured. I'll find that out shortly.  I'm relatively new to MySQL though I'm quite familiar with MS SQL.  As for the first question, I have a form in LibreOffice Base that updates the PackingListDetail table.  When inserting new a record and saving it, the table trigger updates the CommodityNumber field correctly.  However, when subsequently updating that same record in LibreOffice Base, the CommodityNumber field gets assigned a NULL value. There are no other table triggers that assign NULL values to this table.  But if you update that record again in LibreOffice Base, the CommodityNumber field gets updated correctly.  It oscillates back and forth between NULL and the correct value as that record is updated.  I haven't confirmed if the behavior works the same if I run update and insert queries into the table from the MySQL Workbench.  I should have done that first to rule out if LibreOffice Base is making some table calls that I can't currently see.  I don't know if MySQL has a query analyzer type tool like MS SQL to allow you to see what SQL calls are being executed real-time against the MySQL server.  I assume there is.

Dave Stokes-MySQL Community Team-Oracle

Please repost this problem on mysqlcommunity.slack.com and/or forums.mysql.com as the engineers actively monitor the posts.  This is really odd situation.

1 - 3

Post Details

Added on May 18 2020
#mysql-user-groups
3 comments
223 views