Re: How to disable all database triggers??? Thanks

[George Charalampidis]

As Radu Virgil said, this will work, but with a little trick.

First, the right code for your trigger is :

if (@DISABLE_TRIGER IS NULL) then
#trigger body
end if;

I explain :

The parameter DISABLE_TRIGER has a default value NULL.
you can replicate this by the following :
mysql> select @DISABLE_TRIGER;
+-----------------+
| @DISABLE_TRIGER |
+-----------------+
| NULL |
+-----------------+
1 row in set (0.00 sec)

In other words, if you open a mysql command and type the above SELECT, you will get NULL as answer. This is the value that your trigger must check.
So you must have the following code when start your restore script :

SET @DISABLE_TRIGER=1; (or anything you like except NULL)
do imports
SET @DISABLE_TRIGER=NULL;

Why this happens?
When you create a mysql connection (in order to do imports) and set the DISABLE_TRIGER=1, the value you are setting is for yours(distinct) connection to mysql. This value is stored temporarily until you change it (for example set it to NULL) or you quit the connection.
But when your application connects to your mysql it knowns nothing about the DISABLE_TRIGER parameter, so it must check for the DEFAULT value that DISABLE_TRIGER may have (the NULL value).
This is the reason why your trigger must check if the DISABLE_TRIGER has a NULL value.

At the end,
If you do imports (and you have set the @DISABLE_TRIGER=1) your triggers will not run because they check for a NULL value.
If you run your application, your triggers will run because the DISABLE_TRIGER has the DEFAULT value (NULL).

At this time i'm thinking that setting the DISABLE_TRIGER back to NULL value, has not a point at all, because when you close your connection the DISABLE_TRIGER's value will lost. This is only if you do your import and the exit the connection.
But if you want to do further work while you are connected, you must reset the @DISABLE_TRIGER to NULL.

I hope to help somebody.
Thanks