0

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?

3
  • 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 (#1) Does the variable SET statement and further INSERT are executed as one solid multiquery statement? (#2) If your INSERT statement inserts more than one row (or if you execute two INSERTs one-by-one w/o intermediate SET) then all trigger executions except the most first one will see NULL because of your final SET @`entityVersion` := null; statement.
    – Akina
    Oct 11, 2022 at 13:34
  • @Akina: that entityVersion may be null is fine. The question is, why is entityVersion logged as null (the "was null" strings) into the debug_log table, although there is the IF @entityVersion` IS NOT NULL THEN` directly before. Oct 12, 2022 at 14:14
  • The content in the debug_log table is Where the row with UPDATE WAS CALLED is taken from? There is no such INSERT in your trigger... does some another trigger which inserts this line was executed?
    – Akina
    Oct 12, 2022 at 16:13

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.