Sample Header Ad - 728x90

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