In MySQL, a trigger is a set of SQL statements stored in the database directory that are executed or fired whenever an event associated with a table occurs, such as an insert, update, or delete. Triggers are closely related to data tables and are mainly used to protect the data in the tables; especially when there are multiple tables with certain interconnections, triggers can maintain data consistency in different tables. In MySQL, triggers can only be activated when executing INSERT, UPDATE, and DELETE operations, and other SQL statements will not activate triggers.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
Triggers in mysql
In mysql, a trigger is a set of SQL statements stored in the database directory. Whenever A trigger is executed or fired when an event associated with the table occurs, such as an insert, update, or delete.
MySQL triggers, like stored procedures, are programs embedded in MySQL and are powerful tools for data management in MySQL. The difference is that the execution of a stored procedure requires a CALL statement, while the execution of a trigger does not require a CALL statement or manual startup. Instead, it is triggered and activated through related operations on the data table to achieve execution. For example, its execution will be activated when an operation (INSERT, DELETE or UPDATE) is performed on the student table.
Triggers are closely related to data tables and are mainly used to protect the data in the tables. Especially when there are multiple tables that are related to each other, triggers can maintain data consistency in different tables.
In MySQL, triggers can only be activated when performing INSERT, UPDATE, and DELETE operations. Other SQL statements will not activate triggers.
So why use triggers? For example, when actually developing projects, we often encounter the following situation:
When adding a record about students to the student table, the total number of students must change at the same time.
When adding a student record, you need to check whether the age meets the range requirements.
When deleting a student's information, you need to delete the corresponding record on the score sheet.
When deleting a piece of data, you need to keep a backup copy in the database archive table.
Although the business logic implemented in the above situations is different, they all need to automatically perform some processing when the data table changes. At this time, trigger processing can be used. For example, for the first case, you can create a trigger object and perform an operation to calculate the total number of students every time a student record is added. This ensures that every time a student record is added, the total number of students and the number of student records will be calculated. are consistent.
MySQL supported triggers
In actual use, MySQL supports three triggers: INSERT trigger, UPDATE trigger and DELETE trigger.
1) INSERT trigger
A trigger that responds before or after the INSERT statement is executed.
You need to pay attention to the following points when using INSERT triggers:
In the INSERT trigger code, you can reference a virtual table named NEW (case-insensitive) to access the inserted row.
In the BEFORE INSERT trigger, the value in NEW can also be updated, which allows the inserted value to be changed (as long as it has the corresponding operation permissions).
For the AUTO_INCREMENT column, NEW contains the value 0 before the INSERT is executed and will contain the new automatically generated value after the INSERT is executed.
2) UPDATE trigger
A trigger that responds before or after the UPDATE statement is executed.
You need to pay attention to the following points when using UPDATE triggers:
In the UPDATE trigger code, you can reference a virtual table named NEW (case-insensitive) to access the updated value.
Within the UPDATE trigger code, a virtual table named OLD (case-insensitive) can be referenced to access the value before the UPDATE statement was executed.
In the BEFORE UPDATE trigger, the value in NEW may also be updated, which allows changing the value to be used in the UPDATE statement (as long as you have the corresponding operation permissions). All values in
OLD are read-only and cannot be updated.
Note: When the trigger is designed to trigger the update operation of the table itself, only BEFORE type triggers can be used, and AFTER type triggers will not be allowed.
3) DELETE trigger
A trigger that responds before or after the DELETE statement is executed.
You need to pay attention to the following points when using DELETE triggers:
In the DELETE trigger code, you can reference a virtual table named OLD (case-insensitive) to access deleted rows. All values in
OLD are read-only and cannot be updated.
Generally speaking, during the use of triggers, MySQL will handle errors in the following ways.
For transactional tables, if the trigger program fails, and the resulting entire statement fails, then all changes performed by the statement will be rolled back; for non-transactional tables, such rollback cannot be performed, even if the statement Failure, any changes made before the failure will still be effective.
If the BEFORE trigger fails, MySQL will not perform the operation on the corresponding row.
If an error occurs during the execution of the BEFORE or AFTER trigger program, the entire statement calling the trigger program will fail.
MySQL will execute the AFTER trigger only if both the BEFORE trigger and the row operation have been successfully executed.
[Related recommendations:mysql video tutorial]
The above is the detailed content of What is a trigger in mysql. For more information, please follow other related articles on the PHP Chinese website!