How to Disable/Enable Triggers on Demand

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.