How MySQL temporarily disables triggers

How MySQL temporarily disables triggersCause

?? The MySQL trigger, on trigger control, can only trigger control according to the operation mode of the data (Insert,update,delete) and before and after (Before,after) operation. But what if you encounter the following requirements: For an INSERT statement for table A, only data that meets certain criteria triggers an INSERT trigger.

The wording of his own reflex

?? In the corresponding trigger statement, the logic of conditional judgment is increased. Give me a chestnut:
There is a user Information table users, there is an Address Book table addressbook, two table structure similar, business requirements on some data need to do real-time synchronization of data, that is, user updates, addressbook also to update. The user table has an INSERT trigger that inserts only the user data for male gender into the addressbook.
The two tables are structured as follows:

#为方便起见两个表结构就一模一样了，真实环境下一般都是部分字段类似而已#性别字段取值1男0女 CREATE TABLE `user` (    `id` INT(11) NOT NULL AUTO_INCREMENT,    `name` VARCHAR(50) NULL DEFAULT NULL,    `sex` TINYINT(4) NULL DEFAULT ‘0‘,    `age` INT(11) NULL DEFAULT ‘0‘,    `phone` VARCHAR(50) NULL DEFAULT NULL,    `qq` VARCHAR(50) NULL DEFAULT NULL,    PRIMARY KEY (`id`));CREATE TABLE `addressbook` (    `id` INT(11) NOT NULL AUTO_INCREMENT,    `name` VARCHAR(50) NULL DEFAULT NULL,    `sex` TINYINT(4) NULL DEFAULT ‘0‘,    `age` INT(11) NULL DEFAULT ‘0‘,    `phone` VARCHAR(50) NULL DEFAULT NULL,    `qq` VARCHAR(50) NULL DEFAULT NULL,    PRIMARY KEY (`id`));

The insert trigger for the user table is now written as:

CREATE DEFINER=`root`@`localhost` TRIGGER `user_insert_trigger` BEFORE INSERT ON `user` FOR EACH ROW BEGIN    if new.sex = 1     then         insert into addressbook (name,sex,age,qq,phone)             values (new.name,new.sex,new.age,new.qq,new.phone)        ;    end if;END

?? Regular Code Program APE, the first thing that should be thought of is this kind of writing, after all, according to our usual logic to write code. But the demand is often not so simple that the beast will always go in the direction that we cannot anticipate. Give me another chestnut:
Now just sync these users: older than 30 years old, QQ number is less than 8, using 189 mobile phone male users. (╯‵-′) ╯︵┻━┻ (horizontal groove). Now the trigger statement might look like this:

CREATE DEFINER=`root`@`localhost` TRIGGER `user_insert_trigger` BEFORE INSERT ON `user` FOR EACH ROW BEGIN    if new.sex = 1 and  substring(new.phone,1,3)=189 and length(new.qq) <=8 and new.age >30    then         insert into addressbook (name,sex,age,qq,phone)         values (new.name,new.sex,new.age,new.qq,new.phone)             ;    end if;END

?? Not that if more than a few and conditions, more use of a few SQL functions it! Is this the case? What if the phone's judging condition becomes "Fujian Telecom user" (not just the head of No. 189th)? There are more exotic needs: if you want to implement the user table and AddressBook table bidirectional synchronization, yes, the user table and the AddressBook table has an INSERT trigger to the other table plug data, which is not allowed under MySQL (to prevent cyclic triggering). When I met this demand, I even had a hot head. The above method is used to write, before the trigger inserts the data in the table to be inserted does not exist Orz.
To summarize, the above trigger notation has many drawbacks:

1. When there are filtering requirements for triggered data, it is necessary to write many conditional judgments in database language, some of which are well implemented in the programming language, which is very difficult in the database language.
2. Not conducive to understanding and maintenance. With so many logical judgment codes in 1, the statements that lead to triggers are lengthy and complex, and are difficult to read, even with comments added.
3. Not conducive to debugging. Database language After all, unlike other programming languages, do you want to break the point or output the log to see what the reason is not triggered? Pattern, insert data can only be inserted into a temporary table for viewing.
4. If two tables have the logic of adding new data to each other's table, it is either infeasible or dead.

More flexible and elegant notation

?? The method is to use the MySQL session-level variable to control the trigger trigger, and then put the above a bunch of judgment logic in the program code. This is a StackOverflow post found on Google, and it feels very useful.
For a chestnut, the trigger above can be written like this:

CREATE DEFINER=`root`@`localhost` TRIGGER `user_insert_trigger` BEFORE INSERT ON `user` FOR EACH ROW #这里@disable_triggers 是自定义的session变量（mysql中约定session变量用@开头，只对某一次会话有效，不影响其他会话），保证使用时各个触发器名字不一样就好BEGIN    IF @disable_triggers IS NULL THEN        insert into addressbook (name,sex,age,qq,phone)         values (new.name,new.sex,new.age,new.qq,new.phone)        ;    END IF;END

?? If you do not want to trigger a trigger when using the INSERT statement, add the two sentences before and after the SQL statement.

#变量设为非NULL，这样不会进入触发器的相关操作SET @disable_triggers = 1;#不打算触发触发器的insert语句insert into user values(....);#上面语句执行完毕完毕后，重新将变量设置为NULL，重新开启触发逻辑SET @disable_triggers = NULL;

?? For some cases of temporarily disabling triggers when updating data, this method can be used to implement the judgment in the program code, and then decide whether or not to join before and after the SQL statements executed to SET @disable_triggers = 1; SET @disable_triggers = NULL; temporarily prohibit trigger triggering action, the code of the trigger can be very concise and easy to maintain. It is also easy to debug by putting the judgment logic in the program code.

?? In practice, however, it is important to note the following points (the development language I use is Java):

1. SET @disable_triggers = 1;Both SET @disable_triggers = NULL; statements must be placed in the same SQL as the SQL to be executed and then executed together. The data UPDATE statement cannot be executed before execution, SET @disable_triggers = 1; SET @disable_triggers = NULL; because the variables used are session variables, and only in the same session can the trigger find the disable_triggers variable. If the execution is divided into three commits, the equivalent of three sessions, the trigger will get the disable_triggers variable or null.
2. For SQL prepared statement in Java to preprocess the execution of SQL statements, the above method of setting the session variable throws an exception. (preprocessing mode does not support committing multiple SQL statements at once?) I'm using spring's jdbctemplate in my project, and native JDBC should be the same. So using this method to temporarily disable the trigger, or to honestly do the parameter anti-injection judgment, and then splicing SQL statement Execution bar, fortunately this demand scenario will not be many.

How MySQL temporarily disables triggers