How can I delete all the triggers in a MySQL database using one SQL statement?

Question

I have a DB that I have cloned, and now all the logging triggers point to log tables in the original schema. I need to delete all of them in one go (there are several dozen) so that I can recreate them. How can this be done with one command?

Answer 1

SELECT Concat('DROP TRIGGER ', Trigger_Name, ';') FROM  information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_schema';

Copy and paste the generated sql

Answer 2

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]

Answer 3

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_database
user=your_username
password=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 exist

end //
delimiter ;

call drop_a_trigger();

There are two threads about this on the MySQL forums:

• Drop statement in stored procedure
• How to drop trigger in store procedure?

They both reach the same conclusion that even using a stored procedure will not help.

Answer 4

It is not possible to delete all the triggers in a MySQL database using one SQL statement.

But with the following SQL code you can build the list of all the 'DROP TRIGGER' statements (replace <your_schema> with your schema name, e.g. your database name):

-- set `group_concat_max_len`
SET @@session.group_concat_max_len = @@global.max_allowed_packet;

-- select all the triggers and build the `DROP TRIGGER` SQL
-- replace <your_schema> with your schema name (e.g. your database name)
SELECT GROUP_CONCAT(sql_string SEPARATOR '\n')
FROM (
    SELECT CONCAT('DROP TRIGGER IF EXISTS `', TRIGGER_NAME, '`;') AS sql_string,'1'
    FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = '<your_schema>'
    ) AS sql_strings
GROUP BY '1';

The result of the previous query will produce something like:

DROP TRIGGER IF EXISTS `trigger1`;
DROP TRIGGER IF EXISTS `trigger2`;
(...)

And you can use these SQL statements to finally delete all the triggers.

The server system variable group_concat_max_len is the maximum permitted result length in bytes for the GROUP_CONCAT() function. Because the default is 1024, we need to increase it to avoid a truncated result if we have a lot of triggers. I just used the value of the server system variable max_allowed_packet, but usually is fine any big integer number. See this other question.

Answer 5

SELECT Trigger_Name
FROM `information_schema`.`TRIGGERS`
WHERE TRIGGER_SCHEMA = 'your_schema';

copy results to clipboard

convert Trigger_name to DROP TRIGGER <trigger_name>; for each trigger using ^ and S tags for begin and end of line with reg.expressions

run as sql script

Answer 6

Not sure about existence of single command for this purpose.

But here is how i did it some time back

1. Write script to get list of TRIGGERS in db.
2. create Concated list in dropTrigger.sql file
3. Run dropTrigger.SQL file

To get List of triggers you can either use SHOW trigger or Triggers table in Information schema

Answer 7

MAC using MAMP:

There's some small tweaks that need to be done to the accepted answer for us. First we need to target mysql in MAMP. Second, sed -r doesn't work for Mac, use sed -E instead. Assuming the standard MAMP setup, with no changes to root user defaults , just replace [db name] with your db, then copy and paste into Terminal and hit enter.

/Applications/MAMP/Library/bin/mysql -u root -proot --skip-column-names [db name] -e 'SHOW TRIGGERS;' | cut -f1 | sed -E 's/(.*)/DROP TRIGGER IF EXISTS \1;/' | /Applications/MAMP/Library/bin/mysql -u root -proot [db name]

(if you have updated your root password, just swap out "root" after -p with your password

Answer 8

In case you want to drop them using a query without the need to sqldump the database into a file:

select concat('drop trigger ', trigger_name, ';') from information_schema.triggers where trigger_schema = 'your_schema'; You will then need to export the results (I find to CSV is easiest) and run the queries in those results.

Answer 9

The quick answer is "no".

But you can encapsulate logic in one stored procedure: get all triggers and delete all of them in loop using dynamic SQL.

After is you can execute: call delete_all_triggers_api(schema_name);