Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 389 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
Table trigger updates correctly and then back to NULL

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.
Answers
-
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?
Dave Stokes
MySQL Community Manager
-
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.
-
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.
Dave Stokes
MySQL Community Manager