24

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: 

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user810430
  • 11,181
  • 15
  • 38
  • 43

2 Answers2

37

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 NULL before executing the trigger's content. For example:

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;
Francois Deschenes
  • 24,816
  • 4
  • 64
  • 61
  • 8
    I have near 1000 triggers - it very difficult to do what you recomend. – Lorenzo Manucci Jun 25 '11 at 19:47
  • @Lorenzo Manucci - I'm afraid that there isn't a way to disable or enable triggers in MySQL. You should either recreate all of the triggers if this is something you'll need to do on a constant basis or recreate them if they're not working properly. I'm not entirely convinced that modifying the `information_schema` database is a good idea but if it solves your problem and this is a 1 time thing, backup your all your databases and go for it. – Francois Deschenes Jun 25 '11 at 19:57
  • Check the current value using `SELECT @disable_triggers;` – user427969 Feb 19 '15 at 00:52
  • @LorenzoManucci I did a generation script. I actually also something like this: ```sql IF (@DISABLE_ALL_TRIGGERS IS NULL AND @DISABLE_TRIGGER_MOST_YOUR_SPECIFIC_TRIGGER IS NULL) THEN ``` – Tob Aug 08 '22 at 12:42
-1

It is not possible to 'disable' triggers in mysql, however a trick that can be used to get around this

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;

do imports 

SET @DISABLE_TRIGER = 0;
ChrisBint
  • 12,773
  • 6
  • 40
  • 62
  • And also I cannot change the code of triggers because I am not the author - these triggers will fix another person because they work not correct, but I need to finish my work - to test all querys for insert. – Lorenzo Manucci Jun 25 '11 at 19:49