How to disable triggers in MySQL?

79,200

Its not possible to temporarly disable triggers. Do one thing, use one global variable. Trigger will first check value of global variable first. In this case you can change value of global variable to prevent working of trigger.

Share:
79,200
Grijesh Chauhan
Author by

Grijesh Chauhan

I am a web backend developer, mostly write codes in Python, C and Go. I am a postgraduate in Computer Engineering I am a new contributor on Github and HackerRank. I also enjoy teaching and research work. Interesting Reads: Bit Twiddling Hacks Use reentrant functions for safer signal handling SARGable I always read some programming book. Top books in my rack are: Beginning Linux Programming, 4th Edition Python Cookbook, 3rd Edition Python-DataScience-Handbook Text Processing in Python by David Mertz, Amazon.com I think "IPython Cookbook" would be the next book to place on the stack!

Updated on July 09, 2022

Comments

  • Grijesh Chauhan
    Grijesh Chauhan almost 2 years

    In my MySQL database I have some triggers ON DELETE and ON INSERT. Sometimes I need to disable some triggers, and I have to DROP e.g.

    DROP TRIGGER IF EXISTS hostgroup_before_insert //   
    

    and reinstall. Is there any shortcut to SET triggers hostgroup_before_insert = 0 like we have for foreign keys:

    mysql> SELECT version();
    +-------------------------+
    | version()               |
    +-------------------------+
    | 5.1.61-0ubuntu0.10.10.1 |
    +-------------------------+
    1 row in set (0.00 sec)
    

    EDIT Answer There is no built-in server system variable TRIGGER_CHECKS in MySQL.
    A simple workaround is to instead use a user-defined session variable.

    #FALSE value overrides trigger type settings
    SET @TRIGGER_CHECKS = [TRUE|FALSE]; 
    
    SET @TRIGGER_BEFORE_INSERT_CHECKS = [TRUE|FALSE];
    SET @TRIGGER_AFTER_INSERT_CHECKS = [TRUE|FALSE];
    
    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') 
    

    This TRICK is Explained here.