User Comments

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;

@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();

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;

Add your own comment.