Reputation: 14959

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

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?

Upvotes: 43

Answers (9)

Lücks

Reputation: 3994

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

Copy and paste the generated sql

Upvotes: 82

azotov

Reputation: 41

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

Upvotes: 4

Naguib Ihab

Reputation: 4506

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.

Upvotes: 0

cgaldiolo

Reputation: 3867

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.

Upvotes: 9

webaholik

Reputation: 1815

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

Upvotes: 0

Justin Warkentin

Reputation: 10239

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]

Upvotes: 33

Ian Mackinnon

Reputation: 14258

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:

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

Upvotes: 8