Permalink: http://bit.ly/1kBCcQu
One of the sorely wanted features missing in MySQL is the ability
to disable/enable triggers on the fly. By comparison,
disabling/enabling Foreign Key constraints can be simply done by
setting a server system variable:
SET FOREIGN_KEY_CHECKS = [TRUE|FALSE];
Now as of version 5.6, there is no built-in server system
variable TRIGGER_CHECKS in MySQL. A simple workaround is to
instead use a user-defined session variable. The setting for
trigger checks stored into the session variable allows the
setting to be seen by all statements, including all stored
procedures and functions, as long as the user is connected, which
for this workaround is in effect similar to using a server system
variable.
Besides a session variable that switches the checks for all
triggers, to make the control more flexible, we can also set a
session variable for each trigger type. There is a total of 6
trigger types, a combination of [BEFORE|AFTER] with
[INSERT|UPDATE|DELETE]. The total to consider would be 7 session
variables:
#Affects all triggers
#FALSE value overrides trigger type settings
SET @TRIGGER_CHECKS = [TRUE|FALSE];
#Trigger type settings
SET @TRIGGER_BEFORE_INSERT_CHECKS = [TRUE|FALSE];
SET @TRIGGER_AFTER_INSERT_CHECKS = [TRUE|FALSE];
SET @TRIGGER_BEFORE_UPDATE_CHECKS = [TRUE|FALSE];
SET @TRIGGER_AFTER_UPDATE_CHECKS = [TRUE|FALSE];
SET @TRIGGER_BEFORE_DELETE_CHECKS = [TRUE|FALSE];
SET @TRIGGER_AFTER_DELETE_CHECKS = [TRUE|FALSE];
For the disable/enable logic, the only value for the session
variables that matters is the FALSE value. Anything that isn't
FALSE or 0 is considered TRUE. Even NULL values are to be
considered TRUE. It makes sense that the default value for
trigger checks should be TRUE, and when the user disconnects, the
session variable is reset back to NULL which re-enables trigger
checking.
Implementing the ability to disable/enable on existing triggers
could be hard if there are several hundred tables that need to be
altered and with each table having their own unique triggers.
This could especially be painful if there is no easy way to
automate the updating of those existing triggers.
Triggers are special stored procedures and to implement the
trigger disable/enable functionality, a simple logic needs to be
inserted at the BEGIN statement. Here's an example of an existing
BEFORE-INSERT TRIGGER type that has been patched so that it can
be disabled/enabled:
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')
THEN
LEAVE thisTrigger;
END IF;
#Original trigger body starts here
IF (NEW.YEAR > YEAR(NOW()))
THEN
SET NEW.YEAR = NULL;
END IF;
END $$
DELIMITER ;
Here's the rundown on the workaround code patch:
- The BEGIN statement needs to have a label in order to LEAVE the TRIGGER if the trigger checking is SET to FALSE. This facilitates the patching of the functionality into existing TRIGGERs without needing to wrap the original body of the TRIGGER inside the IF-THEN construct.
- The appropriate session variable trigger type needs to be correctly chosen, in order for the specific control variable to be able to turn on/off all the triggers intended for it. In this example, it is correctly coded as @TRIGGER_BEFORE_INSERT_CHECKS.
- We wouldn't want just any user to be able to disable TRIGGERs. Unfortunately, GRANT PRIVILEGES can't cover this case. Either we would need to hardcode the specific USER/s that can disable triggers into the TRIGGER procedure in order to have security or allow only USERs that have the SUPER privilege since disabling triggers is an administrative operation (see this comment for the condition change).
Triggers can CALL stored procedures and its possible that the
trigger logic be moved into an external SP. However, this is not
recommended since it would not be possible to use the special
trigger-specific variables OLD.<columnName>
and NEW.<columnName> in the SP.
When the database administrator logs into the MySQL server under
the user 'root@localhost', they can run DML statements
without invoking any trigger by setting the @TRIGGER_CHECKS
session variable to FALSE. Once done, setting it back to TRUE
re-enables triggers and they can continue with other tasks that
require triggers to fire. They can simply disconnect immediately
without setting the check back to TRUE since in their next
session the triggers are enabled by default.
Another workaround on a missing MySQL functionality, support for
dynamic cursors, can be found here.