We have the following trigger to ensure that a version field is updated in a related table, normally the trigger contains only the UPDATE statement, the INSERTs are added for debugging and to explain the problem:
CREATE TRIGGER `tableB_ins` BEFORE INSERT ON `tableB`
FOR EACH ROW
BEGIN
IF @`entityVersion` IS NOT NULL THEN
INSERT INTO debug_log
VALUES (null, now(), "tableB_ins 1", NEW.taskId, IFNULL(@`entityVersion`, "WAS NULL"), ROW_COUNT());
UPDATE tableA SET entityVersion = @`entityVersion` WHERE taskId = NEW.taskId;
INSERT INTO debug_log
VALUES (null, now(), "tableB_ins 2", NEW.taskId, IFNULL(@`entityVersion`, "WAS NULL"), ROW_COUNT());
SET @`entityVersion` := null;
END IF;
END ;;
The @entityVersion variable is set in a separate request before the INSERT into tableB, which is working as expected.
Sometimes we get the situation, that @entityVersion is not set and is therefore null. But randomly the above UPDATE and the debug INSERTs are still called, which should be prevented by the IF @entityVersion IS NOT NULL clause.
The content in the debug_log table is:
| 155 | 2022-10-11 12:39:18 | tableB_ins 1 | b613 | WAS NULL | -1 |
| 156 | 2022-10-11 12:39:18 | UPDATE WAS CALLED | b613 | 11 | 0 |
| 157 | 2022-10-11 12:39:18 | tableB_ins 2 | b613 | WAS NULL | 1 |
The final question: how can it be, that the inserts are added with "WAS NULL", which means that @entityVersion was null, although the IF NOT NULL is surround the statements? How can that be?
