13.1.18 DROP TRIGGER Syntax

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

This statement drops a trigger. The schema (database) name is optional. If the schema is omitted, the trigger is dropped from the default schema. DROP TRIGGER was added in MySQL 5.0.2. Its use requires the SUPER privilege.

Use IF EXISTS to prevent an error from occurring for a trigger that does not exist. A NOTE is generated for a nonexistent trigger when using IF EXISTS. See Section 13.7.5.37, “SHOW WARNINGS Syntax”. The IF EXISTS clause was added in MySQL 5.0.32.

Triggers for a table are also dropped if you drop the table.

Note

Prior to MySQL 5.0.10, the table name was required instead of the schema name (table_name.trigger_name). When upgrading from a previous version of MySQL 5.0 to MySQL 5.0.10 or newer, you must drop all triggers and re-create them. Otherwise, DROP TRIGGER does not work for older triggers after the upgrade. See Section 2.19.1.1, “Changes Affecting Upgrades to 5.0”, for a suggested upgrade procedure.

In addition, triggers created in MySQL 5.0.16 or later cannot be dropped following a downgrade to MySQL 5.0.15 or earlier. If you wish to perform such a downgrade, you must also in this case drop all triggers prior to the downgrade, and then re-create them afterward.

(For more information about these two issues, see Bug #15921 and Bug #18588.)


User Comments
  Posted by Kevin Regan on March 8, 2007
We really need the "IF EXISTS" functionality. However, we are currently using 5.0.22. So, I came up with the example below. It is a bit complicated because of the lack of anonymous block support.

DROP TABLE IF EXISTS foo;

CREATE TABLE foo(i INTEGER);

DROP PROCEDURE IF EXISTS foo_trigger_deleter;

DELIMITER //

CREATE PROCEDURE foo_trigger_deleter
()
BEGIN
DECLARE l_count INTEGER;

SELECT count(*)
INTO l_count
FROM information_schema.TRIGGERS
WHERE TRIGGER_NAME = 'foo_trigger_deleter';

IF (l_count > 0)
THEN
DROP TRIGGER foo_trigger_deleter;
END IF;
END//

DELIMITER ;

call foo_trigger_deleter();

DROP PROCEDURE foo_trigger_deleter;

CREATE TRIGGER foo_trigger
BEFORE INSERT ON foo
FOR EACH ROW
SET NEW.i = NEW.i + 1;

  Posted by Péter Gergő Barna on March 26, 2008
@Kevin Regan

Supposing one can have several similar schemata (like dev, test, etc.), I think it's better to include the schema in the where clause of the select checking trigger existence:
SELECT count(*)
INTO l_count
FROM information_schema.TRIGGERS
WHERE TRIGGER_NAME = '<trigger_name>'
AND TRIGGER_SCHEMA = SCHEMA();

  Posted by Tim McCormack on November 14, 2010
Combining the ideas from the last two comments, a block of code that will delete ALL triggers from the current schema:

-- Drop all triggers in current schema.

DROP PROCEDURE IF EXISTS trigger_deleter;

DELIMITER $$

CREATE PROCEDURE trigger_deleter()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cur_trig_name VARCHAR(64);
DECLARE trigs CURSOR FOR
SELECT TRIGGER_NAME
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = SCHEMA();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN trigs;
SET done = 0;
drop_loop: LOOP
FETCH trigs INTO cur_trig_name;
IF done THEN
LEAVE drop_loop;
END IF;
DROP TRIGGER cur_trig_name;
END LOOP;
SET done = 1;
CLOSE trigs;
END$$

DELIMITER ;

call trigger_deleter();
DROP PROCEDURE trigger_deleter;
  Posted by NOT_FOUND NOT_FOUND on October 15, 2014
shell command line drop all triggers in one schemata:

mysql -B --column-names=FALSE -e "SELECT CONCAT('DROP TRIGGER ', TRIGGER_NAME, ';') FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = SCHEMA();" yourdatabase

will output the drop statements:

DROP TRIGGER my_table1_aft_ins;
DROP TRIGGER my_table1_aft_upt;
DROP TRIGGER my_table1_aft_delete;
DROP TRIGGER my_table2_aft_insert;
DROP TRIGGER my_table2_aft_update;

you can repipe it to mysql, or text file for editing, etc.

Sign Up Login You must be logged in to post a comment.