How can I delete all the triggers in a MySQL database using one SQL statement?
This is an old question, but since it's the one that keeps popping up in my searches I thought I'd post a solution here. In my case I needed to create a single file that had a full mysqldump
, would then drop any triggers, and then re-add them all. I was able to do it by using the following command to append the DROP TRIGGER
statements to my dump prior to appending the dump of the triggers.
mysql -u [db user] -p[db password] --skip-column-names [db name] -e 'SHOW TRIGGERS;' | cut -f1 | sed -r 's/(.*)/DROP TRIGGER IF EXISTS \1;/' >> dump.sql
To actually drop all the triggers in the same command (as mentioned by @Stephen Crosby in the comments), you can just pipe this back into MySQL like so:
mysql -u [db user] -p[db password] --skip-column-names [db name] -e 'SHOW TRIGGERS;' | cut -f1 | sed -r 's/(.*)/DROP TRIGGER IF EXISTS \1;/' | mysql -u [db user] -p[db password] [db name]
Unfortunately this is not possible in a regular SQL statement or a stored procedure.
Solution
The simplest solution to drop all triggers might be a bash script:
echo "select concat('drop trigger ', trigger_name, ';') from information_schema.triggers where trigger_schema = 'your_database'" | mysql --defaults-extra-file=.mysql.conf --disable-column-names | mysql --defaults-extra-file=.mysql.conf
This requires a credentials file (.mysql.conf
) like so:
[client]database=your_databaseuser=your_usernamepassword=your_password
Reasoning
Attempting to drop a trigger from a stored procedure will fail. I'm guessing this happens because a variable can only hold a string and drop trigger
requires a trigger name (not a string containing a trigger name):
create procedure drop_a_trigger()begin declare var1 varchar(1024); set var1 = "my_second_trigger"; drop trigger my_first_trigger; // OK drop trigger address_update_before; // ERROR 1360 (HY000): Trigger does not existend //delimiter ;call drop_a_trigger();
There are two threads about this on the MySQL forums:
They both reach the same conclusion that even using a stored procedure will not help.