Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.2Mb
PDF (A4) - 31.2Mb
PDF (RPM) - 30.4Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 183.8Kb
Man Pages (Zip) - 295.2Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

13.1.30 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 requires the TRIGGER privilege for the table associated with the trigger.

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.41, “SHOW WARNINGS Syntax”.

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


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.