MySQLContains support for triggers. A trigger is a database object that is related to the table operation. When a specified event occurs on the table of the trigger, the object is invoked, that is, the execution of the trigger on the table that is triggered by the operation event of the table.
Create a trigger
In MySQL, the trigger syntax is created as follows:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
trigger_name：The name of the trigger is identified by the user.
trigger_time：The trigger time of the identification is BEFORE or AFTER.
trigger_event：The trigger event is identified as INSERT, UPDATE or DELETE.
tbl_name：Identifies the name of the table that sets up the trigger, which is to set up the trigger on which table.
trigger_stmt：The trigger program body can be a SQL statement, or multiple statements contained in BEGIN and END.
It can be seen that 6 kinds of triggers can be established, namely, BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE, AFTER INSERT, AFTER UPDATE, AFTER DELETE.
Another limitation is that 2 identical types of triggers can not be set up on one table at the same time, so there are up to 6 triggers on one table.
trigger_event Detailed explanation
MySQL In addition to defining the basic operations of INSERT, UPDATE, and DELETE, the LOAD DATA and REPLACE statements are defined, and these two statements can also trigger the trigger of the above 6 type of trigger.
LOAD DATA Statement is used to load a file into a data table, which is equivalent to a series of INSERT operations.
REPLACE The statement is generally similar to the INSERT statement, only when the primary key or unique index is in the table, if the inserted data is consistent with the original primary key or unique index, the original data will be deleted first.Then add a new data, that is to say, a REPLACE statement is sometimes equivalent to one.
INSERT Statements are sometimes equivalent to a DELETE statement plus a INSERT statement.
INSERT Flip flop: activate a trigger when inserting a row, which may be triggered by INSERT, LOAD DATA and REPLACE statements.
UPDATE Flip-flop: activating trigger when changing a row may trigger by UPDATE statement.
DELETE Type trigger: activate a trigger when deleting a row, which may be triggered by DELETE and REPLACE statements.
BEGIN … END Detailed explanation
In MySQL, BEGIN… The syntax of the END statement is:
BEGIN [statement_list] END
Where statement_list represents a list of one or more statements, each statement in the list must end with a semicolon (()).
In MySQL, the semicolon is the identifier of the end of the statement. When a semicolon is said, the paragraph is finished and MySQL can start executing. Therefore, the interpreter will start executing the semicolon in statement_list and then report the error, because no B is found.EGIN matches the END.
At this point, the DELIMITER command will be used (DELIMITER is the delimiter and separator). It is a command without the need to terminate the identification.
new_delemiter Can be set to 1 or more length symbols, the default is semicolon (), we can modify it to other symbols, such as $:
After that, the semicolon is ended, and the interpreter will not react to it. Only when it meets $, will it be considered the end of the statement. Note that after use, we should remember to revise it.
An example of a complete creation of a trigger
It is assumed that there are two tables in the system:
Class table class (class number classID, number of students in class stuCount)
Student table student (Student ID stuID, class number classID)
To create triggers, the number of classes in class tables can be automatically updated with the addition of students. 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 ;
Variable detailed solution
MySQL DECLARE is used to define a local variable, which can only be used in BEGIN. END compound statement is used and should be defined at the beginning of compound statement.
Before other statements, the syntax is as follows:
DECLARE var_name[,...] type [DEFAULT value]
var_name As variable names, as with SQL statements, 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 a comma; the initial value of the variable is NULL, and if necessary, you can use DEFAULTClauses provide default values, and values can be specified as an expression.
The SET statement is used for the variable assignment, and the syntax is:
SET var_name = expr [,var_name = expr] ...
NEW Detailed explanation with OLD
The NEW keyword is used in the above example, similar to INSERTED and DELETED in MS SQL Server, NEW and OLD are defined in MySQL.
Trigger triggers the row data of the trigger in the table where the trigger is located.
In INSERT flip flops, NEW is used to represent new data that will be inserted (BEFORE) or already (AFTER).
In UPDATE trigger, OLD is used to represent raw data that will be or has been modified, and NEW is used to represent new data that will be or has been modified.
In DELETE trigger, OLD is used to represent raw data that will be or has been deleted.
Usage: NEW.columnName (columnName is a column of corresponding data table).
In addition, OLD is read-only, and NEW can use the SET assignment in the trigger so that it does not trigger the trigger again, causing a loop call, such as adding “2013” before the school number is inserted before each student is inserted.)
Like looking at the database (show databases;) to see the show (tables), the syntax for checking the trigger is as follows:
SHOW TRIGGERS [FROM schema_name];
Among them, schema_name is the name of Schema, which is the same as Schema and Database in MySQL, that is to say, you can specify the database name.
You don’t have to “USE database_name”.
Like deleting the database and deleting the table, the syntax for deleting the trigger is as follows:
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
The execution order of the trigger
The databases we build are generally InnoDB databases, and the tables built on them are transactional tables, that is, transaction security. At this point, if the SQL statement or trigger fails, MySQL will roll back the transaction.
①If the BEFORE trigger fails, SQL cannot execute correctly.
②SQL AFTER triggers do not trigger when execution fails.
③AFTER The type of trigger fails, and SQL rolls back.