Edit Question - KnowledgeHub

Title Body

In my MySQL database I have some triggers ON DELETE and ON INSERT . Sometimes I need to disable some triggers, and I have to DROP e.g. DROP TRIGGER IF EXISTS hostgroup_before_insert // and reinstall. Is there any shortcut to SET triggers hostgroup_before_insert = 0 like we have for foreign keys: mysql> SELECT version(); +-------------------------+ | version() | +-------------------------+ | 5.1.61-0ubuntu0.10.10.1 | +-------------------------+ 1 row in set (0.00 sec) EDIT Answer There is no built-in server system variable TRIGGER_CHECKS in MySQL . A simple workaround is to instead use a user-defined session variable. #FALSE value overrides trigger type settings SET @TRIGGER_CHECKS = [TRUE|FALSE]; SET @TRIGGER_BEFORE_INSERT_CHECKS = [TRUE|FALSE]; SET @TRIGGER_AFTER_INSERT_CHECKS = [TRUE|FALSE]; DELIMITER $$ DROP TRIGGER IF EXISTS `yearCheck_beforeInsert` $$ CREATE DEFINER=`root`@`localhost` TRIGGER `yearCheck_beforeInsert` BEFORE INSERT ON `movies` FOR EACH ROW #Patch starts here thisTrigger: BEGIN IF ((@TRIGGER_CHECKS = FALSE) OR (@TRIGGER_BEFORE_INSERT_CHECKS = FALSE)) AND (USER() = 'root@localhost') This TRICK is Explained here .

Tags (comma-separated) Cancel