How to drop trigger in store procedure?

[Ko Kwang Jin]

Hi :)

I'm trying to write a stored procedure that delete all triggers of a database.


----------------------------------------------------------------------


# Create Store Procedure
DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `dropAllTrigger`$$

CREATE PROCEDURE `dropAllTrigger`()
BEGIN

DECLARE trig VARCHAR(255);
DECLARE cnt INT;
DECLARE trigs CURSOR FOR
SELECT TRIGGER_NAME FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = SCHEMA();

SELECT COUNT( * ) INTO cnt FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = SCHEMA();
IF cnt > 0 THEN
OPEN trigs;
WHILE cnt > 0 DO
FETCH trigs INTO trig;
DROP TRIGGER IF EXISTS trig;
SET cnt = cnt - 1 ;
END WHILE;
CLOSE trigs;
END IF;

END$$

DELIMITER ;


# Execute Store Procedure
CALL dropAllTrigger();


----------------------------------------------------------------------


this procedure is showing error, and is not working.
Do you have any suggestions as to how I can solve this problem?


----------------------------------------------------------------------


(0 row(s) affected, 1 warning(s))
Execution Time : 00:00:00:078
Transfer Time : 00:00:00:000
Total Time : 00:00:00:078
Note Code : 1360
Trigger does not exist