This article uses examples to describe the introduction to MySQL triggers, how to create triggers, and their usage restrictions. Share with you for your reference, the details are as follows: Introduction A SQL trigger is a set of SQL statements stored in the database catalog. A SQL trigger is executed or fired whenever an event associated with a table occurs, such as an insert, update, or delete. SQL triggers can also be considered a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between triggers and stored procedures is that triggers are automatically called when data modification events are performed on a table, while stored procedures must be called explicitly. Now let's look at the advantages of SQL triggers:
Let's look at its shortcomings:
Since triggers are a special type of stored procedure, how do we choose between them? The following is just a suggestion. If we cannot use stored procedures to complete the work, we can consider using SQL triggers. Create a trigger In MySQL, a trigger is a set of SQL statements that are automatically called when changes are made to data on the associated tables. Triggers can be defined to be invoked before or after an insert, update, or delete statement changes data. Prior to MySQL 5.7.2, a maximum of six triggers could be defined per table. Let's take a look at their brief introduction:
However, starting with MySQL 5.7.2+, you can define multiple triggers for the same trigger event and action time. When you use statements that change data in a table without using INSERT, DELETE, or UPDATE statements, the triggers associated with the table are not called. For example, the truncate statement deletes all data in a table but does not invoke triggers associated with the table. However, some statements use the background INSERT statement, such as the REPLACE statement or the LOAD DATA statement. If these statements are used, the corresponding triggers associated with the table are called. So we have to use a unique name for each trigger associated with the table. It is good practice to define the same trigger name for different tables. Let's look at the syntax structure for defining a trigger: (BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE) For example, before_order_update is a trigger that is called before a row in the orders table is updated. Let's look at another way of defining it: tablename_(BEFORE | AFTER)_(INSERT | UPDATE | DELETE) For example, order_before_update is the same as the before_order_update trigger described above. MySQL stores triggers in the data directory, for example: /data/luyaran/, and uses files named tablename.TRG and triggername.TRN:
So we can back up mysql triggers by copying the trigger files to the backup folder, and we can also use the mysqldump tool to back up the triggers. Limitation of Use MySQL triggers cover all the functionality defined in standard SQL, however, there are some limitations on using them in your applications:
Okay, that’s all for this record. Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary" I hope this article will be helpful to everyone's MySQL database design. You may also be interested in:
|
<<: Use shell script to install python3.8 environment in CentOS7 (recommended)
>>: Detailed explanation of using Vue custom tree control
1. Form 1. The role of the form HTML forms are us...
Managing disk space is an important daily task fo...
Preface In Linux kernel programming, you will oft...
This article records the detailed installation pr...
Table of contents What is JSI What is different a...
question Running gdb in docker, hitting a breakpo...
1. Download MySQL 1. Log in to the official websi...
Click here to return to the 123WORDPRESS.COM HTML ...
Login + sessionStorage Effect display After a suc...
background The company code is provided to third ...
Nextcloud is an open source and free private clou...
Because li is a block-level element and occupies ...
When I was in the securities company, because the ...
Preface: After studying the previous article, we ...
Preface This article introduces the fifth questio...