MySQL Trigger Trigger

Creating a trigger: create [definer = {user| Current_user}]trigger trigger_nametrigger_time Trigger_eventon tbl_name for each rowtrigger_body syntax: trigger_ Name: Names of triggers that cannot be duplicated with triggers that already exist; trigger_time:{before | After}, which indicates a trigger before or after an event; trigger_event::{INSERT | UPDATE | DELETE}, the specific event that triggered the trigger; Tbl_name: The trigger acts on the Tbl_name; example: Creating a simple conditioner <1> preparing student tables and student numbers mysql> CREATE TABLE Student_info (-Stu_no INT (one) not null auto_increment, Stu_name VARCHAR (255) DEFAULT null, PRIMARY KEY (stu_ NO);mysql> CREATE TABLE student_count (--Student_count INT (one) DEFAULT 0); Insert a single piece of data:mysql> INSERT INTO Student_count VALUES (0);<2> create a simple trigger that increases the number of students when you insert data into a student table and reduces the number of students when you delete a student http://blog.csdn.net/  Goskalrie/article/details/53020631mysql> CREATE TRIGGER Trigger_student_count_insert after insert-on Student_count for each ROW, UPDATE student_count SET student_count=student_count+1;mysql> CREATE TRIGGER Trigg Er_student_count_insert-After INSERT-in Student_info for each ROW, UPDATE student_count SET student_count=student_count+1;mysql> CREATE TRIGGER Trigger_student_count_delete, after delete, on student_info for each ROW, UPDATE Student_ Count SET student_count=student_count-1;<3> Insert, delete data to see if the trigger works mysql> insert INTO Student_info VALUES (null, ' XIAOC '), (null, ' Xiaoz '), (null, ' Xionan ');mysql> select * from student_info;+--------+----------+| Stu_no |      Stu_name |+--------+----------+| 1 |      XIAOC | | 2 |      Xiaoz | | 3 | Xionan |+--------+----------+3 rows in Set (0.00 sec) mysql> desc student_info;+----------+--------------+------+--- --+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+--------------+------+-----+---------+----------------+| Stu_no | Int (11) | NO | PRI | NULL | auto_increment | | Stu_name | varchar (255) |     YES | |                NULL | |+----------+--------------+------+-----+---------+----------------+2 rows in Set (0.00 sec) to remove the correct generator: The trigger will be deleted as the table is deleted!     View trigger: show triggers;:mysql> CREATE TABLE student_info (stu_no INT) not NULL auto_increment, Stu_name VARCHAR (255) DEFAULT NULL,----PRIMARY KEY (stu_no), Q Uery OK, 0 rows affected (0.22 sec) mysql> CREATE TABLE student_count (Student_count INT (one) Defau LT 0); Query OK, 0 rows affected (0.24 sec) mysql> INSERT into Student_count VALUES (0);    Query OK, 1 row affected (0.14 sec) mysql> CREATE TRIGGER Trigger_student_count_insert, after insert -I-student_info for each ROW, UPDATE student_count SET student_count=student_count+1;q    Uery OK, 0 rows affected (0.09 sec) mysql> CREATE TRIGGER trigger_student_count_delete, after delete -I-student_info for each ROW,-> UPDATE Student_count SET student_count=student_count-1; Query OK, 0 rows affected (0.14 sec) mysql> INSERT into Student_info VALUES (null, ' XIAOC '), (null, ' Xiaoz '), (null, ' Xionan ') ‘); Query OK, 3 rows affected (0.05 sec) records:3 duplicates:0 warnings:0mysql> select * from student_info;+--------+- ---------+| Stu_no |      Stu_name |+--------+----------+| 1 |      XIAOC | | 2 |      Xiaoz | | 3 | Xionan |+--------+----------+3 rows in Set (0.00 sec) mysql> Select * from student_count;+---------------+|             Student_count |+---------------+| 3 |+---------------+1 row in Set (0.00 sec) mysql> Delete from Student_info where stu_name in (' Xionan ', ' Xiaoc '); Query OK, 2 rows affected (0.06 sec) mysql> select * from student_count;+---------------+|             Student_count |+---------------+| 1 |+---------------+1 row in Set (0.00 sec) mysql> INSERT into student_info values (null, ' Xiaol '); Query OK, 1 row affected (0.06 sec) mysql> select * from student_info;+--------+----------+| Stu_no |      Stu_name |+--------+----------+| 2 |      Xiaoz | | 4 | Xiaol |+--------+----------+2 rows in Set (0.00 sec) mysql> Select * from student_count;+---------------+|             Student_count |+---------------+| 2 |+---------------+1 row in Set (0.00 sec) You can see that the number of students will change with either insert or delete student. Create a trigger that contains multiple execution statements: Multiple SQL statements can be executed in Trigger_body, at which point the trigger_body needs to use begin and end as the starting and ending flags: create [definer = {User | Current_User}] TRIGGER trigger_name trigger_time Trigger_eventon tbl_name for each rowbegintrigger_statementend; Example 2 , create a trigger that contains multiple execution statements: (delete trigger) mysql> show triggers\g;*************************** 1.               Row *************************** Trigger:trigger_student_count_insert Event:insert Table:student_info statement:update student_count SET student_count=student_count+1 Timing:aft ER created:null sql_mode:strict_trans_tables,no_engine_substitution definer: [EMAIL&N BSp;protected]character_set_client:utf8collation_connection:utf8_general_ci Database collation:latin1_swedish_ci* 2.               Row *************************** Trigger:trigger_student_count_delete Event:delete Table:student_info statement:update student_count SET student_count=student_count-1 Timing:aft ER created:null sql_mode:strict_trans_tables,no_engine_substitution definer: [EMAIL&N Bsp;protected]character_set_client:utf8collation_connection:utf8_general_ci Database Collation:latin1_swedish_ Ci2 rows in Set (0.00 sec) error:no Query specifiedmysql> DROP TRIGGER trigger_student_count_insert; Query OK, 0 rows affected (0.10 sec) mysql> DROP TRIGGER trigger_student_count_delete; Query OK, 0 rows Affected (0.00 sec) mysql> Show triggers; The Empty Set (0.00 sec) still follows the table in the example above, making the following changes to the Student_count table: Increase the Student_class field to represent the number of students in a specific grade, where 0 is the full grade and 1 represents the 1 grade ... In the same student table, the field is also added. Clears all the numbers in two tablesAccording to 1: Add Student_class field to Student_count table:mysql> Show CREATE table student_count;+---------------+-------------------- -----------------------------------------------------------------------------------------+| Table | Create Table |+------------ ---+----------------------------------------------------------------------------------------------------------- --+| Student_count | CREATE TABLE ' student_count ' (' student_count ' int (one) ' default ' 0 ') engine=innodb default charset=latin1 |+-------------- -+------------------------------------------------------------------------------------------------------------- +1 row in Set (0.00 sec) mysql> ALTER TABLE student_count add Student_class int (one) default ' 0 '; Query OK, 0 rows affected (0.33 sec) records:0 duplicates:0 warnings:0mysql> Show CREATE TABLE student_count;+----- ----------+----------------------------------------------------------------------------------------------------------------------------------------------------+| Table |                                                                                                                                       Create Table |+---------------+------------------------------------------------------------------------------- ---------------------------------------------------------------------+| Student_count | CREATE TABLE ' student_count ' (' student_count ' int (one) ' default ' 0 ', ' student_class ' int (one) ' default ' 0 ') Engine=innodb D Efault charset=latin1 |+---------------+------------------------------------------------------------------------ ----------------------------------------------------------------------------+1 Row in Set (0.00 sec) <1> Delete the two triggers in the previous example, initialize the data in the Student_count table, insert three data (0,0), (1,0), (2,0) The initial number of the year, first grade, and second year is 0;mysql> update student_count set student_count=0; Query OK, 1 row affected (0.07 sec) Rows matched:1 changed:1 warnings:0mysql> sElect * from student_count;+---------------+---------------+| Student_count |             Student_class |+---------------+---------------+|             0 | 0 |+---------------+---------------+1 row in Set (0.00 sec) mysql> Insert Student_count values (1,0), (2,0); Query OK, 2 rows affected (0.07 sec) records:2 duplicates:0 warnings:0mysql> select * from student_count;+--------- ------+---------------+| Student_count |             Student_class |+---------------+---------------+|             0 |             0 | |             1 |             0 | |             2 | 0 |+---------------+---------------+3 rows in Set (0.00 sec) <2> create trigger, first increase the total number of students in insert, and then determine the grade of the new student, Increase the total number of students in the corresponding grade:mysql> select * from student_info;+--------+----------+| Stu_no |      Stu_name |+--------+----------+| 2 |      Xiaoz | | 4 | Xiaol |+--------+----------+2 rows in Set (0.00 sec) mysql> Delete from student_info where stu_name= ' xiaoz '; Query OK, 1 row affected (0.03 sec) mysql> Delete from StudeNt_info where stu_name= ' Xiaol '; Query OK, 1 row affected (0.06 sec) mysql> select * from Student_info; Empty Set (0.00 sec) above is the first to clean up the data: Field increase error, order error:mysql> ALTER TABLE Student_count drop Student_count; Query OK, 0 rows affected (0.36 sec) records:0 duplicates:0 warnings:0mysql> desc student_count;+---------------+-- -------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------------+---------+------+-----+---------+-------+| Student_class | Int (11) |     YES | |       0 | |+---------------+---------+------+-----+---------+-------+1 row in Set (0.00 sec) mysql> ALTER TABLE Student_count Add Student_count int (one) default ' 0 '; Query OK, 0 rows affected (0.41 sec) records:0 duplicates:0 warnings:0mysql> Show CREATE TABLE student_count;+----- ----------+---------------------------------------------------------------------------------------------------- ------------------------------------------------+| Table | Create TAble |+---------------+--------------------------------------------------------------------------------------- -------------------------------------------------------------+| Student_count | CREATE TABLE ' student_count ' (' student_class ' int (one) ' default ' 0 ', ' student_count ' int (one) ' default ' 0 ') Engine=innodb D Efault charset=latin1 |+---------------+------------------------------------------------------------------------ ----------------------------------------------------------------------------+1 Row in Set (0.00 sec) mysql> Delete from Student_count where student_class=0; Query OK, 3 rows affected (0.06 sec) reinsert the data:mysql> insert Student_count values (0,0), (1,0), (2,0); Query OK, 3 rows affected (0.08 sec) records:3 duplicates:0 warnings:0mysql> select * from student_count;+--------- ------+---------------+| Student_class | Student_count |+---------------+---------------+|             0 |             0 | |             1 |             0 | |             2 | 0 |+---------------+---------------+3 rows in Set (0.00 sec) http://blog.csdn.net/goskalrie/article/details/ 53020631 make the following changes to the Student_count table: Increase the Student_class field to indicate the number of students in the specific grade, 0 of which is the full grade and 1 for the 1 grade ... In the same student table, the field is also added. Clears all data from the two tables. Mysql> ALTER TABLE student_info add column Student_class int//query OK, 0 rows affected (1.31 sec) records:0 Duplicate s:0 warnings:0mysql> desc student_info//+---------------+--------------+------+-----+---------+--------------- -+| Field | Type | Null | Key | Default | Extra |+---------------+--------------+------+-----+---------+----------------+| Stu_no | Int (11) | NO | PRI | NULL | auto_increment | | Stu_name | varchar (255) |     YES | |                NULL | || Student_class | Int (11) |     YES | |                NULL | |+---------------+--------------+------+-----+---------+----------------+3 Rows in Set (0.00 sec) Creates a trigger that first increases the total number of students at insert, then judges the total number of students, then judges the number of new students in the grade, and then increases the total of students in the corresponding grade.  Mysql> delimiter $ $mysql > CREATE trigger Trigger_student_count_insert after insert, on Student_info    For each row, begin, update student_count set student_count=student_count+1 where student_class=0;    -Update student_count set student_count=student_count+1 where Student_class=new.student_class; -End---$ $Query OK, 0 rows affected (0.09 sec) mysql> delimiter; Create a trigger that first reduces the total number of students in the delete, and then determines if the deleted student is a grade, and then reduces the corresponding year Total number of students;mysql> DELIMITER $ $mysql > mysql> CREATE TRIGGER trigger_student_count_delete-After DELE TE---on Student_info-----UPDATE Student_count SET St    Udent_count=student_count-1 WHERE student_class=0;    UPDATE student_count SET student_count=student_count-1 WHERE student_class= old.student_class; --END----&Gt $ $Query OK, 0 rows affected (0.15 sec) mysql> mysql> DELIMITER;mysql> desc student_info//+---------------+------ --------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------------+--------------+------+-----+---------+----------------+| Stu_no | Int (11) | NO | PRI | NULL | auto_increment | | Stu_name | varchar (255) |     YES | |                NULL | || Student_class | Int (11) |     YES | |                NULL | |+---------------+--------------+------+-----+---------+----------------+3 rows in Set (0.00 sec) Insert multiple student information from different grades into the student table to see if the trigger is done with:mysql> insert into Student_info VALUES (null, ' AAA ', 1), (null, ' BBB ', 1), (null, ' CCC ') , 2), (null, ' DDD ', 2), (null, ' ABB ', 1), (null, ' ACC ', 1); Query OK, 6 rows affected (0.12 sec) records:6 duplicates:0 warnings:0mysql> select * from student_info;+--------+- ---------+---------------+| Stu_no | Stu_name | Student_class |+--------+----------+---------------+| 1 |             AAA |      1 | | 2 |             BBB |      1 | | 3 |             CCC |      2 | | 4 |             DDD |      2 | | 5 |             ABB |      1 | | 6 |             ACC | 1 |+--------+----------+---------------+6 rows in Set (0.00 sec) mysql> Select * from student_count;+---------------+- --------------+| Student_class |             Student_count |+---------------+---------------+|             1 |             4 | |             2 |             2 | |             0 | 6 |+---------------+---------------+3 rows in Set (0.00 sec) can be seen, a total of 6 data inserted, the total number of students is 6, 1 grade 4, 2 grade 2, trigger correct execution. From the student table, apart from a number of different grades of student information, to see if the trigger is working:mysql> delete from student_info where stu_name like ' A% '; Query OK, 3 rows affected (0.15 sec) mysql> SELECT * from student_info;+--------+----------+---------------+| Stu_no | Stu_name |      Student_class |+--------+----------+---------------+| 2 |             BBB |      1 | | 3 |             CCC |      2 | | 4 | DdD | 2 |+--------+----------+---------------+3 rows in Set (0.00 sec) mysql> Select * from student_count;+---------------+- --------------+| Student_class |             Student_count |+---------------+---------------+|             1 |             1 | |             2 |             2 | |             0 | 3 |+---------------+---------------+3 rows in Set (0.00 sec) Removes the student information from the student table with the name beginning with a, while the student information is deleted, and the quantity table follows the change. In the example above, three new keywords were used: DELIMITER, new, old, these three keywords are described in the "Trigger syntax" section of the official web

MySQL Trigger Trigger