MySQL DROP TRIGGER statement explained

The MySQL DROP TRIGGER statement is used to remove triggers that exist in your database schema.

For example, suppose you have a table named cities as follows:

CREATE TABLE `cities` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` tinytext,
  PRIMARY KEY (`id`)  
);

Then, you create a BEFORE INSERT trigger to concatenate the city_name column value with the string City as follows:

CREATE TRIGGER `before_insert_city` 
  BEFORE INSERT ON `cities`
  FOR EACH ROW 
  SET NEW.city_name =  CONCAT(NEW.city_name, " City")

This trigger will automatically add the word City next to the city name that you want to insert to the table.

The following INSERT statement:

INSERT INTO `cities` VALUES (null, "Liverpool");

Will insert the following data to the table:

+----+-----------------+
| id | city_name       |
+----+-----------------+
|  1 | Liverpool City  |
+----+-----------------+

A MySQL trigger persists on your database schema even after you end your current MySQL session.

As long as the trigger exists, it will be executed when the condition for running the trigger is “triggered”. In this case, an INSERT statement into the cities table.

To remove the trigger, you need to run a DROP TRIGGER statement followed by the trigger name as shown below:

DROP TRIGGER `before_insert_city`;

Running a DROP TRIGGER statement when the trigger doesn’t exist will cause an error as follows:

mysql> DROP TRIGGER `before_insert_city`;

ERROR 1360 (HY000): Trigger does not exist

To prevent that error, you can add the IF EXISTS clause before the trigger name as follows:

DROP TRIGGER IF EXISTS `before_insert_city`;

The statement above won’t cause an error even when the trigger doesn’t exist.

Finally, a trigger is attached to the database where the table exists. You need to select the database where you want to remove the trigger with the USE statement:

USE [database_name];

Or you can add the database name before the trigger name in the DROP TRIGGER statement. Because my cities table is created in the test_db database, the syntax will be as follows:

DROP TRIGGER IF EXISTS `test_db`.`before_insert_city`;

And that’s how the DROP TRIGGER statement works in MySQL.

Feel free to copy and customize the statements above for your project 😉