MySQL IF NULL of a variable is not working as expected in a trigger
0
votes
0
answers
708
views
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?
Asked by Thomas Lauria
(101 rep)
Oct 11, 2022, 11:04 AM
Last activity: Oct 11, 2022, 11:25 AM
Last activity: Oct 11, 2022, 11:25 AM