Tutorial on disabling and enabling triggers in MySQL [Recommended]

Tutorial on disabling and enabling triggers in MySQL [Recommended]

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?
As the name suggests, a trigger is an SQL statement that is automatically called by the database under certain conditions. The trigger rejects the manual calling process and is automatically called by the MYSQL database, which makes the execution more efficient.

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:
  • MySQL trigger syntax and application examples
  • Detailed explanation of MySql view trigger stored procedure
  • MySQL trigger principle and usage example analysis
  • MySQL log trigger implementation code
  • MySQL trigger trigger add, delete, modify and query operation example
  • MySQL triggers: creating multiple triggers operation example analysis
  • MySQL triggers: creating and using triggers
  • Introduction to MySQL triggers, creation of triggers and analysis of usage restrictions
  • MySQL trigger definition and usage simple example
  • Detailed explanation of the concept, principle and usage of MySQL triggers
  • Detailed explanation of MySQL database triggers

<<:  Detailed explanation of function classification and examples of this pointing in Javascript

>>:  Linux disk sequential writing and random writing methods

Recommend

MYSQL master-slave replication knowledge points summary

An optimization solution when a single MYSQL serv...

How to build your own Nexus private server in Linux

This article describes how to build a Nexus priva...

Sharing ideas on processing tens of millions of data in a single MySQL table

Table of contents Project Background Improvement ...

Summary of SQL deduplication methods

When using SQL to extract data, we often encounte...

Detailed analysis and usage of tcpdump command under Linux

Introduction To put it simply, tcpdump is a packe...

Issues with Rancher deployment and importing K8S clusters

Rancher deployment can have three architectures: ...

Implementing a puzzle game with js

This article shares the specific code of js to im...

Docker installation of Nginx problems and error analysis

question: The following error occurred when insta...

Analysis of the principles and usage of Linux hard links and soft links

In the Linux system, there is a kind of file call...

Analysis of the process of implementing Nginx+Tomcat cluster under Windwos

Introduction: Nginx (pronounced the same as engin...

MySQL batch adding and storing method examples

When logging in to the stress test, many differen...

Docker completely deletes private library images

First, let’s take a look at the general practices...