MySQL MySQL禁用所有触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6480074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL disable all triggers
提问by user810430
For test correctness of query I need disable all triggers in db. I see that in information_schema exists table TRIGGERS. Is possible temporarily disable all triggers using this table? E.g. like:
为了测试查询的正确性，我需要禁用数据库中的所有触发器。我看到在 information_schema 中存在表 TRIGGERS。是否可以使用此表暂时禁用所有触发器？例如：
update TRIGGERS set TRIGGERS_SCHEMA='myschema_new'
where TRIGGERS_SCHEMA='myschema'
and after finish all test return all triggers like:
并在完成所有测试后返回所有触发器，例如：
update TRIGGERS set TRIGGERS_SCHEMA='myschema'
where TRIGGERS_SCHEMA='myschema_new'
May be this can corrupt db or after triggers will not works? I didn't found about it in documentation.
可能这会损坏数据库还是触发器不起作用？我没有在文档中找到它。
回答by Francois Deschenes
You can't disable triggers directly and I wouldn't recommend doing what you're suggesting but you could have your trigger check if a variable (in my example below @disable_triggers) is NULLbefore executing the trigger's content. For example:
您不能直接禁用触发器，我不建议您执行您建议的操作，但您可以在执行触发器内容之前让触发器检查变量（在我下面的示例中@disable_triggers）是否存在 NULL。例如：
Query:
询问：
SET @disable_triggers = 1;
// Your update statement goes here.
SET @disable_triggers = NULL;
Triggers:
触发器：
IF @disable_triggers IS NULL THEN
// Do something use as the trigger isn't disabled.
END IF;
回答by ChrisBint
It is not possible to 'disable' triggers in mysql, however a trick that can be used to get around this
在 mysql 中“禁用”触发器是不可能的，但是可以使用一个技巧来解决这个问题
Add a condition in your triggers like:
在触发器中添加一个条件，例如：
if (DISABLE_TRIGER <> 1 ) then
#trigger body
end if;
and than if you want to disable triggers on import just:
而不是如果您只想在导入时禁用触发器：
SET @DISABLE_TRIGER = 1;
设置@DISABLE_TRIGER = 1;
do imports
做进口
SET @DISABLE_TRIGER = 0;
