MySQL 5.1 includes support for triggers. A trigger is a database object associated with a table operation that is called when a specified event occurs on the table on which the trigger is located . , that is, the execution of a trigger on a table's action event. "CREATE TRIGGER" in MySQL, the CREATE TRIGGER syntax is as follows:CREATE TRIGGER trigger_name trigger_time trigger_eventOn tbl_name for each ROW trigger_stmt among them: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, 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 the INSERT, UPDATE, and DELETE basic operations, MySQL also defines the LOAD DATA and REPLACE statements, which can also cause trigger of the above 6 type trigger. 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 if you have primary key or unique index in the table, the inserted data and the original primary when a key or a unique index is consistent, the original data is deleted and a new data is added, which means that a REPLACE statement is sometimes equivalent to a INSERT statements, sometimes equivalent to a DELETE statement plus an INSERT statement. therefore: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 of the detailed " in MySQL, BEGIN ... The syntax for the END statement is:BEGIN[Statement_list]ENDwhere 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 The semicolon is executed after the statement_list, and then the error is reported 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_delemiterNew_delemiter can be set to 1 or more length symbols, the default is a semicolon (;), we can modify it to other symbols, such as pipe characters:DELIMITER |after that, the statement ends with a semicolon, and the interpreter does not react, only the end of the statement is considered to have encountered a pipe character. Note that after use, we also You should 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 |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 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[,...] Type [DEFAULT value] among them:Var_name is a variable name, and as with SQL statements, variable names are 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 a value that can be specified as an expression. A SET statement is used to assign values to variables, with the following syntax:SET var_name = expr [, 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, which does not trigger the trigger again, causing the loop to be called (such as every insertion of a learning "2013" in front of their school number before they were born. "View triggers" and view the database (show databases;) to view the table (show tables;), the syntax for viewing the trigger is as follows:SHOW TRIGGERS [from schema_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:DROP TRIGGER [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 is missing, MySQL will roll back the transaction, there are:① SQL fails to execute correctly if before trigger execution failsThe After trigger does not fire when the ②sql execution fails③after type of trigger execution fails and SQL is rolled back
