MySQL trigger usage

MySQL includes support for triggers. A trigger is a database object related to table operations. When a specified event occurs on the table where the trigger is located, the object will be called, that is, the operation event of the table triggers the execution of the trigger on the table.

Create a trigger
In MySQL, the syntax for creating a trigger is as follows:

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

Among them:

trigger_name: identifies the trigger name, specified by the user;
trigger_time: identifies the triggering time, the value is BEFORE or AFTER;
trigger_event: identifies the trigger event, the value is INSERT, UPDATE or DELETE;
tbl_name: identifies the table name to create the trigger, that is, on which table the trigger is created;
trigger_stmt: trigger program body, which can be a SQL statement, or use BEGIN and END contain multiple statements.

It can be seen that 6 types of triggers can be created, namely: BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE, AFTER INSERT, AFTER UPDATE, AFTER DELETE.

Another limitation is that you cannot create 2 triggers of the same type on a table at the same time, so a maximum of 6 triggers can be created on a table.

trigger_event detailed explanation
MySQL except for INSERT, UPDATE, DELETE In addition to defining basic operations, it also defines LOAD DATA and REPLACE statements, which can also cause the triggering of the above 6 types of triggers.

The LOAD DATA statement is used to load a file into a data table, which is equivalent to a series of INSERT operations.

The REPLACE statement is generally very similar to the INSERT statement, except that when there is a primary key or unique index in the table, if the inserted data is different from the original When the primary key or unique index is consistent, the original data will be deleted first, and then a new piece of data will be added. In other words, a REPLACE statement is sometimes equivalent to one.

INSERT statement is sometimes equivalent to a DELETE statement plus an INSERT statement.

INSERT type trigger: The trigger is activated when a row is inserted, and may be triggered by INSERT, LOAD DATA, REPLACE statements;
UPDATE Type trigger: The trigger is activated when a certain row is changed, which may be triggered by the UPDATE statement;
DELETE type trigger: The trigger is activated when a certain row is deleted, which may be triggered by Triggered by DELETE and REPLACE statements.

BEGIN … END Detailed explanation
In MySQL, the syntax of the BEGIN … END statement is:

BEGIN
[statement_list]
END
Among them, statement_list Represents a list of one or more statements. Each statement in the list must be terminated with a semicolon (;).
In MySQL, the semicolon is the identifier of the end of the statement. Encountering a semicolon means that the statement has ended and MySQL can start execution. Therefore, the interpreter encounters statement_list Execution starts after the semicolon in , and then an error is reported because no END matching BEGIN is found.

The DELIMITER command will be used at this time (DELIMITER is the delimiter, which means separator). It is a command and does not require an end-of-statement identifier. The syntax is:
DELIMITER new_delemiter
new_delemiter It can be set to 1 or more length symbols. The default is semicolon (;). We can change it to other symbols, such as $:
DELIMITER $
The statement after this ends with a semicolon, and the interpreter will not react. Only when $ is encountered, the statement is considered to have ended. Note that after using it, we should remember to modify it back.

A complete example of creating a trigger
Suppose there are two tables in the system:
Class table class(class number classID, number of students in the class stuCount)
Student table student (student ID, classID)
To create a trigger to automatically update the number of students in the class table as students are added, the code is as follows:

DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;

Detailed explanation of variables
Use DECLARE in MySQL to define a Local variable, this variable can only be used in BEGIN ... END It is used in compound statements and should be defined at the beginning of compound statements,

that is, before other statements. The syntax is as follows:

DECLARE var_name[,...] type [DEFAULT value]
where:
var_name is the variable name, the same as SQL The statements are the same, variable names are not case-sensitive; type is any data type supported by MySQL; multiple variables of the same type can be defined at the same time, separated by commas; the initial value of the variable is NULL, if necessary, you can use The DEFAULT clause provides a default value, which can be specified as an expression.

Use SET statement for variable assignment, the syntax is:

SET var_name = expr [,var_name = expr] ...

NEW and OLD detailed explanation

The NEW keyword is used in the above example, and in MS SQL Server INSERTED and DELETED are similar. NEW and DELETED are defined in MySQL. OLD, used to represent

In the table where the trigger is located, the row of data that triggered the trigger.
Specifically:
In INSERT type triggers, NEW Used to represent new data that will be (BEFORE) or has been (AFTER) inserted;
In UPDATE type triggers, OLD is used to represent the original data that will be or has been modified, NEW Used to represent new data that will be or has been modified;
In DELETE type triggers, OLD is used to represent the original data that will be or has been deleted;
Usage: NEW.columnName (columnName is a column name of the corresponding data table)
In addition, OLD is read-only, while NEW can use SET in triggers Assign a value so that the trigger will not be triggered again and cause a circular call (for example, before inserting a student, add "2013" before its student number).

Viewing triggers

is the same as viewing databases (show databases;) and viewing tables (show tables;). The syntax of viewing triggers is as follows:

SHOW TRIGGERS [FROM schema_name];
where, schema_name is the name of the Schema, In MySQL Schema and Database are the same, that is to say, you can specify the database name, so you don't have to "USE database_name;" first.

Delete triggers

Same as deleting databases and tables, the syntax of deleting triggers is as follows:

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

The execution order of triggers

The database we created It is generally an InnoDB database, and the tables created on it are transactional tables, that is, transaction safe. At this time, if the SQL statement or trigger fails to execute, MySQL The transaction will be rolled back, including:

① If the BEFORE trigger fails to execute, SQL cannot be executed correctly.

②When SQL execution fails, the AFTER trigger will not fire.

③AFTER If a trigger of type fails to execute, SQL will roll back.

Do you guys have some understanding of the use of mysql triggers? If you have any questions, please leave me a message and we can make progress together.