When using MYSQL, triggers are often used, but sometimes improper use can cause some trouble. Is there a way to control the calling of triggers? How to disable the trigger? 1. Create two new tables: Table demo_1: CREATE TABLE `demo_1` ( `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key', `STUNAME` varchar(32) DEFAULT NULL COMMENT 'Name', `AGE` tinyint(4) DEFAULT NULL COMMENT 'Age', PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8; Table: demo_2 CREATE TABLE `demo_2` ( `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key', `STU_ID` int(11) DEFAULT NULL COMMENT 'Student ID', `MATH` double DEFAULT NULL COMMENT 'score', PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; 2. Then create a trigger for demo_1 DROP TRIGGER IF EXISTS `insertTragger`; DELIMITER ;; CREATE TRIGGER `insertTragger` AFTER INSERT ON `demo_1` FOR EACH ROW begin select ID INTO @v1 from demo_1 order by ID desc limit 1; Insert into demo_2(STU_ID, MATH) values(@v1, 98.5); end ;; DELIMITER ; 3. The trigger is: when a piece of data is added in demo_1, a piece of data is automatically added in demo_2. 4. But I don't want to trigger the trigger every time data is input or output. I only want it to be triggered when I want it to be triggered. Rewrite the trigger: DROP TRIGGER IF EXISTS `insertTragger`; DELIMITER ;; CREATE TRIGGER `insertTragger` AFTER INSERT ON `demo_1` FOR EACH ROW begin if @enable_trigger = 1 then select ID INTO @v1 from demo_1 order by ID desc limit 1; Insert into demo_2(STU_ID, MATH) values(@v1, 98.5); end if; end ;; DELIMITER ; 5. Call trigger SET @enable_trigger = 1; INSERT INTO demo_1(STUNAME , AGE) VALUES('Xiao Qiang', 17); After execution, a data entry 2 Xiaoqiang17 is added to the table demo_1 Table demo_2 also adds a data 2 2 98.5 6. Disable the trigger SET @enable_trigger = 0; INSERT INTO demo_1(STUNAME , AGE) VALUES('Xiao Qiang', 17); After execution: After execution, a data entry 2 Xiaoqiang17 is added to the table demo_1 No data is added to table demo_2 The above solves the problem of flexible calling of triggers. Summarize The above is the tutorial on disabling and starting triggers in MySQL that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website! You may also be interested in:
|
<<: Detailed explanation of function classification and examples of this pointing in Javascript
>>: Linux disk sequential writing and random writing methods
An optimization solution when a single MYSQL serv...
This article describes how to build a Nexus priva...
Table of contents Project Background Improvement ...
When using SQL to extract data, we often encounte...
Table of contents 1. ChildNodes attribute travers...
Introduction To put it simply, tcpdump is a packe...
Rancher deployment can have three architectures: ...
This article shares the specific code of js to im...
question: The following error occurred when insta...
In the Linux system, there is a kind of file call...
Preface Today I will share with you a holy grail ...
Introduction: Nginx (pronounced the same as engin...
When logging in to the stress test, many differen...
First, let’s take a look at the general practices...
Why is the title of the article “Imitation Magnif...