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.
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
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`;
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
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 😉