Check if a variable was set from SELECT INTO in MySQL Triggers

Question

I'm not sure if this is already answered or not, but I couldn't find a good solution.

I have a trigger for my table (source_table), where it will populate another table (target_table). However, when populating the target_table, I need to get a value from another table (intermediate_table). But I need to INSERT INTO my target_table only if the value from source_table is available in the intermediate_table.

My current trigger looks like this.

DROP TRIGGER IF EXISTS after_source_table_insert;
DELIMITER $$
CREATE TRIGGER after_source_table_insert AFTER INSERT
 ON source_table
 FOR EACH ROW
 BEGIN
    DECLARE my_id INT(11);
    SELECT some_id INTO my_id FROM intermediate_table it WHERE it.some_id = NEW.another_id;
    IF my_id <> NULL THEN -- CONDITION ---
      INSERT INTO `target_table` (`target_id`, `updated_at`)
      VALUES (my_id, NOW())
      ON DUPLICATE KEY UPDATE   `updated_at` = NOW();
    END IF;
 END;
$$

However, the CONDITION doesn't seem to work, even when the my_id is valid.

What is the best way to execute the INSERT query if the SELECT populates my_id?

Is using select FOUND_ROWS(); a good option here?

NOTE: I don't want to do the SELECT more than once because the tables are very big.

Answer 1

• NULL values can be compared either using IS NULL or IS NOT NULL.
• Also, I have set the default value of my_id to NULL, to avoid unintended cases. It is generally a good practice to set default values for the declared variables.

Try the following:

DECLARE my_id INT(11) DEFAULT NULL;
SELECT some_id INTO my_id FROM intermediate_table it WHERE it.some_id = NEW.another_id;
IF my_id IS NOT NULL THEN -- CONDITION ---
  INSERT INTO `target_table` (`target_id`, `updated_at`)
  VALUES (my_id, NOW())
  ON DUPLICATE KEY UPDATE   `updated_at` = NOW();
END IF;