如何在MySQL中禁用触发器? [英] How to disable triggers in MySQL?

问题描述

在我的MySQL数据库中，我有一些触发器ON DELETE和ON INSERT.有时我需要禁用一些触发器，并且必须DROP例如

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 //   

，然后重新安装. SET triggers hostgroup_before_insert = 0是否有任何快捷键，如外键一样?

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)

编辑答案 MySQL中没有没有内置服务器系统变量TRIGGER_CHECKS .
一个简单的解决方法是改为使用用户定义的会话变量.

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

此 TRICK是在这里解释 .

推荐答案

无法临时禁用触发器.做一件事，使用一个全局变量.触发器将首先检查全局变量的值.在这种情况下，您可以更改全局变量的值以防止触发器起作用.

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.

这篇关于如何在MySQL中禁用触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！