如何在 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 触发 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屋！