Testing MySQL Triggers with the LIKE Clause and a More Reliable Approach

Using the LIKE clause to filter triggers or views in MySQL is common but can be deceptive, especially in non‑interactive sessions where the output is not visible. The article first creates a simple lab with three tables: two data tables ( test_lab and test2lab ) and a log table ( log_lab ) that records inserts.

SQL to create the tables:

CREATE TABLE `test_lab` (
  `id` int unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `test2lab` (
  `id` int unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `log_lab` (
  `changed_table` varchar(50) NOT NULL,
  `id` int unsigned NOT NULL,
  PRIMARY KEY (`changed_table`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Two identical AFTER INSERT triggers are then added to copy each new row into log_lab :

DELIMITER //
CREATE TRIGGER `test_lab_trigger_INS` AFTER INSERT ON `test_lab`
FOR EACH ROW BEGIN
  INSERT INTO log_lab (`changed_table`,`id`) VALUES ('test_lab', NEW.id);
END//

CREATE TRIGGER `test2lab_trigger_INS` AFTER INSERT ON `test2lab`
FOR EACH ROW BEGIN
  INSERT INTO log_lab (`changed_table`,`id`) VALUES ('test2lab', NEW.id);
END//
DELIMITER ;

Inserting a row into each table and selecting from log_lab confirms that the triggers fire correctly:

INSERT INTO `test_lab` VALUES (8);
INSERT INTO `test2lab` VALUES (2);
SELECT * FROM log_lab;
+---------------+----+
| changed_table | id |
+---------------+----+
| test_lab      |  8 |
| test2lab      |  2 |
+---------------+----+

When querying the information_schema.TRIGGERS table with LIKE 'test2lab' , both triggers appear because the underscore in the table name is treated as a wildcard character. The result shows that LIKE does not perform a strict string match.

SHOW TRIGGERS LIKE 'test2lab';
-- returns rows for both test_lab_trigger_INS and test2lab_trigger_INS

The article explains that using the placeholder _ matches any single character, which is why the pattern matches both table names. To obtain an exact match, the article recommends using a WHERE Table = 'test2lab' clause instead of LIKE :

SHOW TRIGGERS WHERE `Table` = 'test2lab';
-- returns only the trigger for test2lab

This reliable method avoids the pitfalls of LIKE and ensures that only the intended trigger definitions are retrieved. The conclusion emphasizes the importance of understanding how operators work in LIKE expressions, building quick labs to verify command behavior, and preferring exact‑match alternatives when necessary.