MySQL explains in simple terms how to use triggers

This article brings you relevant knowledge about mysql. Triggers are a method provided by SQLserver to programmers and data analysts to ensure data integrity. It is related to table events. The related special stored procedures and events were introduced after MySQL 5.1. They are somewhat similar to the scheduled tasks of the operating system, but periodic tasks are built-in and executed on the MySQL server. Let's take a look at them together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

In actual development, we often encounter such a situation: there are 2 Or multiple interrelated tables, such as product information and inventory information are stored in two different data tables respectively. When we add a new product record, in order to ensure the integrity of the data, we must also add a new product record to the inventory table. Inventory records. In this way, we must write these two associated operation steps into the program and wrap them with transactions to ensure that these two operations become an atomic operation, either all of them are executed or none of them are executed.

If you encounter special circumstances, you may need to manually maintain the data, so it is easy to forget one step, resulting in data loss. At this time, we can use triggers. You can create a trigger so that the insertion of product information data automatically triggers the insertion of inventory data. In this way, you don’t have to worry about missing data due to forgetting to add inventory data.

Trigger overview

MySQL supports triggers starting from version 5. 0. 2. MySQL triggers, like stored procedures, are programs embedded in the MySQL server. A trigger is an operation triggered by an event, including INSERT, UPDATE, and DELETE events.

The so-called event refers to the user's action or triggering a certain behavior. If a trigger program is defined, when the database executes these statements, it is equivalent to an event occurring, and the trigger will be automatically triggered to perform the corresponding operation. When performing insert, update, and delete operations on data in a data table and need to automatically perform some database logic, triggers can be used to achieve this.

Creation of triggers

Create trigger syntax

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 
触发器执行的语句块;

Description:

Table name: Indicates the object monitored by the trigger.

BEFORE | AFTER: Indicates the trigger time. BEFORE means trigger before the event; AFTER means trigger after the event.

INSERT | UPDATE | DELETE: Indicates the triggered event.

INSERT means it is triggered when a record is inserted;

UPDATE means it is triggered when a record is updated;

DELETE means it is triggered when a record is deleted.

Code example 1

Create two tables

CREATE TABLE test_trigge r (
id INT PRIMARY KEY AUTO_INCREMENT ,
t_note VARCHAR ( 30 )
) ;
CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT ,
t_log VARCHAR ( 30 )
) ;

Create a trigger

DELIMITER / /
CREATE TRIGGER befo_re_insert
BEFORE INSERT ON test_trigger 
FOR EACH ROW
BEGIN
 INSERT INTO test_trigger_log ( t_log )
 VALUES ( ' befo re_inse rt ' ) ;
END / /
DELIMITER ;

Insert data into the test_trigger data table

INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');

View the data in the test_trigger_log data table

SELECT * FROM test_trigger_log

##Code example 2

Create a trigger

DELIMITER / /
CREATE TRIGGER after_insert
AFTER INSERT ON test_trigger
FOR EACH ROW
BEGIN
 INSERT INTO test_trigger_log ( t_log )
 VALUES ( ' after_insert ' ) ;
END / /
DELIMITER ;

Insert data into the test_trigger data table.

INSERT INTO test_trigger (t_note) VALUES ('测试 AFTER INSERT 触发器');

View the data in the test_trigger_log data table

SELECT * FROM test_trigger_log

Code example 3

Define the trigger "salary_check_trigger", based on the employee table" employees" INSERT event, before INSERT, check whether the salary of the new employee to be added is greater than the salary of his leader. If it is greater than the salary of the leader, an error of sqlstate_value being 'HY000' will be reported, causing the addition to fail.

DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
DECLARE mgrsalary DOUBLE;
SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
IF NEW.salary > mgrsalary THEN
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
END IF;
END //
DELIMITER ;

The NEW keyword in the trigger declaration process above represents the new record of the INSERT statement.

View deletion triggers

Method 1: View the definition of all triggers in the current database

SHOW TRIGGERS

Method 2: View the definition of a trigger in the current database

SHOW CREATE TRIGGER 触发器名

Method 3: Query the "salary_check_trigger" trigger information from the TRIGGERS table of the system library information_schema.

SELECT * FROM information_schema.TRIGGERS;

Delete trigger

DROP TRIGGER IF EXISTS 触发器名称

Advantages of triggers

1. Triggers can ensure data integrity.

Suppose we use the purchase order header table (demo.importhead) to save the overall information of the purchase order, including the purchase order number, supplier number, warehouse number, total purchase quantity, total purchase amount and acceptance date.

Use the purchase order details table (demo.importdetails) to save the details of the purchased goods, including the purchase order number, product number, and purchase quantity

The quantity, purchase price and purchase amount are not equal to the total quantity and total amount in the purchase order details. This is a data inconsistency.

In order to solve this problem, we can use triggers to stipulate that whenever there is data insertion, modification and deletion in the purchase order detail table

, the 2-step operation will be automatically triggered:

1) Recalculate the total quantity and total amount in the purchase order detail table;

2) Use the values ​​calculated in the first step to update the total quantity and total amount in the purchase order header table.

In this way, the total quantity and total amount in the purchase order header table will always be the same as the total quantity and

calculated in the purchase order detail table. The data is consistent and does not conflict with each other.

2. Triggers can help us record operation logs.

Using triggers, you can specifically record what happened at what time. For example, recording the trigger for modifying the member's stored value amount is a very good example. This is very helpful for us to restore the specific scenario when the operation is performed and better locate the cause of the problem.

3. Triggers can also be used to check the validity of data before operating it.

For example, when a supermarket purchases goods, the warehouse manager needs to enter the purchase price. However, it is easy to make mistakes in human operations. For example, when entering the quantity

, you scan the barcode; when entering the amount, you read the serial number, and the entered price far exceeds the selling price, resulting in a loss on the books. Huge losses...

These can all be used through triggers to check the corresponding data before the actual insertion or update operation, prompt errors in time, and prevent

wrong data enter the system.

Disadvantages of triggers

1. One of the biggest problems of triggers is poor readability.

Because triggers are stored in the database and driven by events, this means that triggers may not be controlled by the application layer. This is very challenging for system maintenance.

For example, create a trigger to modify member recharge operations. If there is a problem with the operation in the trigger, the update of the member's stored value amount will fail. I use the following code to demonstrate

The result shows that the system prompts an error and the field "aa" does not exist.

This is because the data insertion operation in the trigger has one more field, and the system prompts an error. However, if you don't understand this trigger, you may think that there is a problem with the update statement itself, or there is a problem with the structure of the member information table. Maybe you will also add a field called "aa" to the member information table to try to solve this problem, but the result will be in vain.

2. Changes in related data may cause trigger errors.

Especially changes in the data table structure may cause trigger errors, thus affecting the normal operation of data operations. These will affect the efficiency of troubleshooting the cause of errors in the application due to the concealment of the trigger itself.

Notes

Note that if a foreign key constraint is defined in the child table, and the foreign key specifies the ON UPDATE/DELETE CASCADE/SET NULL clause, modifying the parent table will be referenced. When the key value or the record row referenced by the parent table is deleted, it will also cause modification and deletion operations of the child table. At this time, the triggers defined based on the UPDATE and DELETE statements of the child table will not be activated.

For example: The DELETE statement based on the child table employee table (t_employee) defines trigger t1, and the department number (did) field of the child table defines a foreign key constraint that refers to the parent table department table (t_department). The primary key column is the department number (did), and the foreign key has the "ONDELETE SET NULL" clause added. Then if the parent table department table (t_department) is deleted at this time, the child table employee table (t_employee)

Recommended learning :

mysql video tutorial

The above is the detailed content of MySQL explains in simple terms how to use triggers. For more information, please follow other related articles on the PHP Chinese website!