MySQL Trigger trigger usage three (slightly complicated)

MySQL includes support for triggers. A trigger is a database object associated with a table operation that invokes the execution of a trigger on a table when a specified event occurs on the table on which the trigger is located.

Create a Trigger

In MySQL, the CREATE TRIGGER syntax is as follows:

TRIGGER on Tbl_name for each  rowtrigger_stmt   

which

Trigger_name: Identifies the trigger name, which is specified by the user;
Trigger_time: Identification trigger time, value is before or after;
Trigger_event: Identifies the triggering event, with a value of INSERT, UPDATE, or DELETE;
Tbl_name: Identifies the name of the table on which the trigger is established, that is, the table on which the trigger is established;
TRIGGER_STMT: A trigger program body, which can be an SQL statement, or multiple statements containing the BEGIN and END.

Thus, there are 6 types of triggers that can be created: before insert, before UPDATE, before DELETE, after INSERT, after UPDATE, and after DELETE.

Another limitation is that you cannot create 2 triggers of the same type on a table at the same time, so there are up to 6 triggers on a table.

Trigger_event detailed

In addition to defining INSERT, UPDATE, and DELETE basic operations, M Ysql also defines the LOAD DATA and REPLACE statements, which can also trigger triggers of the above 6 types.

The load data statement is used to load a file into a data table rather than a series of INSERT operations.

The REPLACE statement is generally similar to the INSERT statement, except that 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 is deleted and then a new data is added, which means , a REPLACE statement is sometimes equivalent to a single clause.

Insert statements, sometimes equivalent to a DELETE statement plus an INSERT statement.

Insert trigger: Activates a trigger when inserting a row, which may be triggered by an insert, LOAD DATA, and REPLACE statement;
UPDATE trigger: Activates a trigger when a row is changed and may be triggered by an UPDATE statement;
Delete trigger: Activates a trigger when a row is deleted and may be triggered by a delete, REPLACE statement.

BEGIN ... END detailed

In MySQL, BEGIN ... The syntax for 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 end-of-statement identifier, and a semicolon indicates that the segment statement has ended and MySQL can start executing. Therefore, the interpreter encounters a semicolon in the statement_list and then begins execution, and then reports an error because no END is found that matches the BEGIN.

This will use the DELIMITER command (DELIMITER is the delimiter, the meaning of the delimiter), it is a command, do not need a statement to end the identity, the syntax is:
DELIMITER New_delemiter
New_delemiter can be set to 1 or more length symbols, the default is a semicolon (;), we can modify it to other symbols, such as $:
DELIMITER $
After that, the statement ends with a semicolon, and the interpreter does not react, only if it encounters $, it is considered to be the end of the statement. Note that after use, we should also remember to change 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 (School number Stuid, class number ClassID)
To create a trigger that automatically updates the number of students in the class table as the student adds, the code is as follows:

DELIMITER $CreateTrigger Tri_stuinsert afterInsertOn studentFor each Rowbegindeclare C Span style= "COLOR: #0000ff" >int; Set c = (select stucount  From class where classid= NEW.CLASSID); update class set stucount = C + 1  where ClassID = New.classid; End $DELIMITER;            

variable explanation

MySQL uses DECLARE to define a local variable that can be used only at BEGIN ... END compound statement, and should be defined at the beginning of the compound statement,

That is, before the other statements, the syntax is as follows:

DECLARE var_name[,... [DEFAULT value]    

which
Var_name is the variable name, as with the SQL statement, the variable name is not case-sensitive; Type is any data type supported by MySQL; You can define multiple variables of the same type at the same time, separated by commas, the initial value of the variable is NULL, and if necessary, you can use the default clause to provide the defaults. The value can be specified as an expression.

A SET statement is used to assign values to variables, with the following syntax:

[, var_name = expr]  ...

NEW and Old detailed

The new keyword is used in the above example, similar to INSERTED and DELETED in MS SQL Server, where new and old are defined in MySQL to represent

The row of data that triggers the trigger in the same table as the trigger.
In particular:
In an INSERT trigger, new is used to represent the before or already (after) inserted data;
In an UPDATE trigger, old is used to represent the original data that will or has been modified, and new is used to represent the data that will or has been modified;
In a delete type trigger, old is used to denote the original data that will or has been deleted;
How to use: New.columnname (ColumnName is a column name for the corresponding data table)
In addition, old is read-only, and NEW can use SET assignment in a trigger so that the trigger is not triggered again, causing a circular call (such as adding "2013" to the student's number before each insert).

View triggers

and view the database (show databases;) to view the table (show tables;), the syntax for viewing the trigger is as follows:

[Fromschema_name]; 

Where Schema_name is the name of the schema, in MySQL, the schema and database are the same, that is, you can specify the name of the databases, so that

Without first "use database_name;" The

Delete Trigger

As with deleting a database, deleting a table, the syntax for deleting a trigger is as follows:

[IF EXISTS[schema_name.] Trigger_name   

Execution order of triggers

The database we build is generally a InnoDB database, and the tables created on it are transactional tables, which are transaction-safe. At this point, if the SQL statement or trigger execution fails, MySQL rolls back the transaction, with:

① if the before trigger execution fails, SQL does not execute correctly.
The after type trigger does not fire when the ②sql execution fails.
The ③after type of trigger execution fails and SQL is rolled back.

MySQL Trigger trigger usage three (slightly complicated)