mysql触发器disable,如何在MySQL中禁用触发器？

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')

解决方案

Its not possible to temporarly disable triggers. Do one thing, use one global variable. Trigger will first check value of global variable first. In this case you can change value of global variable to prevent working of trigger.