MySQL :: MySQL 5.6 リファレンスマニュアル

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / ... / DROP TRIGGER 構文

13.1.30 DROP TRIGGER 構文

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

このステートメントは、トリガーを削除します。スキーマ (データベース) 名はオプションです。スキーマが省略されている場合、このトリガーはデフォルトスキーマから削除されます。DROP TRIGGER には、このトリガーに関連付けられたテーブルに対する TRIGGER 権限が必要です。

存在しないトリガーに対してエラーが発生しないようにするには、IF EXISTS を使用します。IF EXISTS を使用している場合は、存在しないトリガーに対して NOTE が生成されます。セクション13.7.5.41「SHOW WARNINGS 構文」を参照してください。

テーブルを削除すると、そのテーブルのトリガーも削除されます。

PREV HOME UP NEXT

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.