Introduction to the use and advantages and disadvantages of MySQL triggers

Preface

In actual development, we often encounter such a situation: there are two or more interrelated tables, such as product information and inventory information are stored in two different data tables. When we add a new product record, in order to ensure the integrity of the data, we must also add an inventory record to the inventory table.

In this case, we must write these two related operation steps into the program and wrap them with transactions to ensure that the two operations become an atomic operation, either all are executed or none are executed.

If you encounter special circumstances, you may need to manually maintain the data, so it is easy to forget one of the steps and cause data loss. At this time, we can use triggers.

You can create a trigger so that the insert operation of product information data automatically triggers the insert operation of inventory data. This way, you don’t have to worry about missing data due to forgetting to add inventory data.

1. 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 event that triggers an action, including INSERT, UPDATE, and DELETE events. An event refers to a user's action or the triggering of a behavior. If a trigger 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 inserting, updating, and deleting data in a data table and some database logic needs to be executed automatically, triggers can be used to implement it.

2. Creation of triggers

2.1 Create trigger syntax

The syntax for creating a trigger is:

CREATE TRIGGER trigger name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table name FOR EACH ROW
The statement block executed by the trigger;

illustrate:

①Table name: indicates the object monitored by the trigger.

②BEFORE|AFTER: indicates the trigger time. BEFORE means triggering before the event; AFTER means triggering after the event.

③INSERT|UPDATE|DELETE: indicates the triggered event.

INSERT means inserting a record;

UPDATE means updating records;

DELETE means deleting a record.

④ The statement block executed by the trigger: It can be a single SQL statement or a compound statement block composed of BEGIN...END structure.

2.2 Code Examples

Example:

①Create a data table:

CREATE TABLE test_trigger (
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: Create a trigger named before_insert, and insert the before_insert log information into the test_trigger_log data table before inserting data into the test_trigger data table.

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

③Insert data into the test_trigger data table

INSERT INTO test_trigger (t_note) VALUES ('test BEFORE INSERT trigger');

④View the data in the test_trigger_log data table

mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log |
+----+---------------+
| 1 | before_insert |
+----+---------------+
1 row in set (0.00 sec)

3. View and delete triggers

3.1 View triggers

Viewing triggers means viewing the definition, status, and syntax information of triggers that already exist in the database.

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

SHOW TRIGGERS\G

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

SHOW CREATE TRIGGER trigger name

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

SELECT * FROM information_schema.TRIGGERS;

3.2 Deleting a trigger

Triggers are also database objects. To delete a trigger, use the DROP statement. The syntax is as follows:

DROP TRIGGER IF EXISTS trigger name;

4. Advantages and disadvantages of triggers

4.1 Advantages

① Triggers can ensure the integrity of data.

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, purchase quantity, purchase price and purchase amount.

Whenever we enter, delete or modify a purchase order detail data, the data in the purchase order detail table will change. At this time, the total quantity and total amount in the purchase order header table must be recalculated, otherwise, the total quantity and total amount in the purchase order header table will not be equal to the total quantity in the purchase order details table.

The calculation and amount are added together, which is the inconsistency in the data.

To solve this problem, we can use triggers to automatically trigger two steps whenever there is data insertion, modification, or deletion in the purchase order details table:

1) Recalculate the total quantity and total amount in the purchase order details 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 values ​​in the purchase order header table will always be the same as the total quantity and total amount values ​​calculated in the purchase order details table, and the data will be consistent and will not contradict each other.

② Triggers can help us record operation logs.

Using triggers, you can record exactly what happened and when. For example, recording the trigger for modifying the member deposit amount is a good example. This is very helpful for us to restore the specific scenario when the operation was executed and better locate the cause of the problem.

③ Triggers can also be used to check the legitimacy of data before operating it.

For example, when a supermarket purchases goods, the warehouse manager is required to enter the purchase price. However, it is easy to make mistakes in human operations. For example, when entering the quantity, the barcode is scanned in; when entering the amount, the line is misread and the price entered is far higher than the selling price, resulting in huge losses on the books. These can be done through triggers,

Or before the update operation, check the corresponding data and prompt errors in time to prevent erroneous data from entering the system.

4.2 Disadvantages

①The biggest problem with triggers is poor readability.

Because triggers are stored in the database and are event-driven, 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 the member deposit operation. If there is a problem with the operation in the trigger, the member stored value update will fail. I use

The following code demonstrates this:

mysql> update demo.membermaster set memberdeposit=20 where memberid = 2;
ERROR 1054 (42S22): Unknown column 'aa' in 'field list'

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 an extra field and the system prompts an error. However, if you don't understand this trigger, you might 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 give

I tried to solve this problem by adding a field called "aa" to the member information table, but it was a waste of effort.

②Changes in related data may cause trigger errors.

In particular, changes in the data table structure may cause trigger errors, thereby affecting the normal operation of data operations. These will affect the efficiency of troubleshooting the causes of errors in the application due to the hidden nature of the triggers themselves.

4.3 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, then modifying the referenced key value of the parent table or deleting the referenced record row of the parent table will also cause modification and deletion operations on the child table.

The trigger defined by the statement will not be activated.

This is the end of this article about the use and advantages and disadvantages of MySQL triggers. For more relevant MySQL trigger content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!