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 TRIGGER
privilege for the table associated with the trigger. (This
statement requires the SUPER privilege prior to
MySQL 5.1.6.)
Use IF EXISTS to prevent an error from
occurring for a trigger that does not exist. A
NOTE is generated for a non-existent trigger
when using IF EXISTS. See
Section 13.5.4.31, “SHOW WARNINGS Syntax”. The IF EXISTS
clause was added in MySQL 5.1.14.
Note: When upgrading from a version of MySQL
older than MySQL 5.0.10 to 5.0.10 or newer — including all
MySQL 5.1 releases — you must drop all triggers
before upgrading and re-create them
afterward, or else DROP TRIGGER does not work
after the upgrade. See Section 2.11.1, “Upgrading from MySQL 5.0 to 5.1”, for a
suggested upgrade procedure.
DROP TRIGGER cannot be used together with the handy MYSQL extension "IF EXISTS". Other 5.0 features, like PROCEDUREs and FUNCTIONs however, do have this feature.
If you want to test for the existence of a trigger before removing it, you can do the following test before you delete it:
SELECT COUNT(*) FROM information_schema.TRIGGERS where TRIGGER_NAME = {yourtriggerhere};
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;
User Comments
A DROP TRIGGER fails if the table doesn't exists, so drop tiggers before.
DROP TRIGGER cannot be used together with the handy MYSQL extension "IF EXISTS". Other 5.0 features, like PROCEDUREs and FUNCTIONs however, do have this feature.
If you want to test for the existence of a trigger before removing it, you can do the following test before you delete it:
SELECT COUNT(*) FROM information_schema.TRIGGERS where TRIGGER_NAME = {yourtriggerhere};
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;
Add your own comment.