MySQL learning-triggers, MySQL learning-triggers

MySQL learning-triggers, MySQL learning-triggers

MySQL supports triggers. A trigger is a database object related to table operations. When a specified event occurs on the table where the trigger is located, this object is called, that is, the Operation Event of the table triggers the execution of the trigger on the table.

Create a trigger

In MySQL, the trigger creation syntax is as follows:

CREATE TRIGGER trigger_nametrigger_timetrigger_event ON tbl_nameFOR EACH ROWtrigger_stmt

Where:

Trigger_name: identifies the trigger name, which is specified by the user;
Trigger_time: indicates the trigger time. The value is BEFORE or AFTER;
Trigger_event: identifies a trigger event. The value can be INSERT, UPDATE, or DELETE;
Tbl_name: name of the table on which the trigger is created;
Trigger_stmt: the trigger program body, which can be an SQL statement or multiple statements contained by BEGIN and END.

We can see that six triggers can be created: before insert, before update, before delete, after insert, after update, and after delete.

Another restriction is that two triggers of the same type cannot be created on a table at the same time. Therefore, a maximum of six triggers can be created on a table.

Trigger_event details

In addition to defining basic INSERT, UPDATE, and DELETE operations, MySQL also defines the load data and REPLACE statements. These two statements can also trigger the triggers 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.

In general, the REPLACE statement is similar to the INSERT statement, but when the table has a primary key or unique index, 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, a REPLACE statement is sometimes equivalent to one.

INSERT statements are sometimes equivalent to adding an INSERT statement to a DELETE statement.

INSERT trigger: the trigger is activated when a row is inserted. It may be triggered by INSERT, load data, and REPLACE statements;
UPDATE trigger: the trigger is activated when a row is changed, which may be triggered by the UPDATE statement;
DELETE trigger: the trigger is activated when a row is deleted, which may be triggered by the DELETE or REPLACE statements.

BEGIN... END details

In MySQL, BEGIN... The END statement syntax is:

BEGIN[statement_list]END

Statement_list indicates the 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. If a semicolon is encountered, it indicates that the statement has ended and MySQL can start to execute. Therefore, if the interpreter encounters a semicolon in statement_list, the interpreter starts execution and reports an error because the END matching the in is not found.

In this case, the DELIMITER command is used (DELIMITER is the DELIMITER), which is a command and does not need to end the statement identifier. Syntax:
DELIMITER new_delemiter
New_delemiter can be set to one or more characters in length. The default value is semicolon (;). We can change it to another symbol, such as $:
DELIMITER $
The statement after this ends with a semicolon, and the interpreter does not respond. Only when $ is encountered, it is considered that the statement ends. Note: after use, we should remember to change it back.

A complete trigger creation example

Assume that the system has two tables:
Class table class (class number classID, stuCount in the class)
Student (student ID, class ID)
Create a trigger to automatically update the number of students in the class table with the addition of students. The Code is as follows:

DELIMITER $create trigger tri_stuInsert after inserton student for each rowbegindeclare 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 details

MySQL uses DECLARE to define a local variable... The END compound statement must be defined at the beginning of the compound statement,

The syntax is as follows:

DECLARE var_name[,...] type [DEFAULT value]

Where:
Var_name is the variable name. Like SQL statements, the variable name is case-insensitive. type is any data type supported by MySQL. Multiple variables of the same type can be defined at the same time and separated by commas; the initial value of the variable is NULL. You can use the DEFAULT clause to provide the DEFAULT value. The value can be specified as an expression.

The SET statement is used to assign values to variables. Syntax:

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

NEW and OLD

The NEW keyword is used in the example above, similar to INSERTED and DELETED in the ms SQL Server, MySQL defines NEW and OLD to represent

In the table where the trigger is located, the row of data that triggers the trigger.
Specifically:
In an INSERT trigger, NEW indicates the NEW data to be inserted (BEFORE) or AFTER;
In the UPDATE trigger, OLD is used to indicate the original data to be or has been modified, and NEW is used to indicate the NEW data to be or has been modified;
In the DELETE trigger, OLD is used to indicate the original data to be deleted or deleted;
Usage: NEW. columnName (columnName is a column name of the corresponding data table)
In addition, OLD is read-only, while NEW can be used in the trigger to assign values, so that the trigger will not be triggered again, resulting in a loop call (for example, every time a student is inserted, add "2013" before the student ID ").

View triggers

Like viewing a table (show tables;) in a database (show databases;), the syntax for viewing a trigger is as follows:

SHOW TRIGGERS [FROM schema_name];

Schema_name is the Schema name. in MySQL, the Schema is the same as the Database, that is, you can specify the Database name.

"USE database_name;" is not required first.

Delete trigger

The syntax for deleting a trigger is as follows:

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

Trigger execution sequence

The databases we create are generally InnoDB databases, and the tables created on them are transactional tables, that is, transaction security. At this time, if the SQL statement or trigger fails to be executed, MySQL will roll back the transaction, including:

① If the BEFORE trigger fails to be executed, the SQL statement cannot be correctly executed.
② When SQL Execution fails, the AFTER trigger will not be triggered.
③ If an AFTER trigger fails to be executed, the SQL statement will be rolled back.

 

From: http://blog.163.com/csbqf@126/blog/static/6122437120131875924806/