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

Solution to Linux CentOS 6.5 ifconfig cannot query IP

Recently, some friends said that after installing...

...

Mysql implementation of full-text search and keyword scoring method example

1. Introduction Today a colleague asked me how to...

How to install nginx in docker and configure access via https

1. Download the latest nginx docker image $ docke...

Seven Principles of a Skilled Designer (1): Font Design

Well, you may be a design guru, or maybe that'...

Summary of naming conventions for HTML and CSS

CSS naming rules header: header Content: content/c...

Implementation example of react project from new creation to deployment

Start a new project This article mainly records t...

How to use async await elegantly in JS

Table of contents jQuery's $.ajax The beginni...

Detailed explanation of MySQL Strict Mode knowledge points

I. Strict Mode Explanation According to the restr...

Vue implements star rating with decimal points

This article shares the specific code of Vue to i...

How to install Nginx in CentOS7 and configure automatic startup

1. Download the installation package from the off...

In-depth explanation of modes and environment variables in Vue CLI

Preface In the development of actual projects, we...

Tomcat multi-instance deployment and configuration principles

1. Turn off the firewall and transfer the softwar...