Summary of creation and use of mysql--triggers

Original http://blog.csdn.net/goskalrie/article/details/53020631

What is a trigger

The trigger (TRIGGER) is one of MySQL's database objects and is supported starting with version 5.0.2. This object is very similar to a function in a programming language and needs to be declared, executed, and so on. However, the execution of the trigger is not called by the program, nor is it started manually, but is triggered and activated by the event to achieve execution. Something like an event in the DOM.

So why use database object triggers? In a specific development project, you will often encounter the following examples:

<1> have field student name in Student table, field student total, each time a student's information is added, the total number of students must be changed at the same time.

<2> in the student table will also have the student name abbreviation, student address and other fields, when adding student information, often need to check the phone, mailbox and other formats are correct.

The above example uses the trigger to complete with this feature, need to automatically do some processing when the table changes. MySQL automatically executes the action that is set when the Delete/update/insert statement is triggered, and other SQL statements do not activate the trigger.

Create a Trigger

Use the Help command to view the specific syntax:

CREATE     = {User | Current_User}]    TRIGGER trigger_name    trigger_time trigger_event on tbl_name for each    ROW    trigger_body

In grammar

Trigger_name: The name of the trigger and cannot be duplicated with the trigger that already exists;

trigger_time:{before | After}, which indicates a trigger before or after the event;

trigger_event::{INSERT | UPDATE | DELETE}, the specific event that triggered the trigger;

Tbl_name: The trigger acts on the tbl_name;

Create a simple trigger

Example 1, creating a simple trigger

<1> Prepare student table and number of student statistics

CREATE TABLE student_info (  stu_no INT (one) not NULL auto_increment,  stu_name VARCHAR (255 ) DEFAULT NULL,  PRIMARY KEY (stu_no)); CREATE TABLE student_count (  student_count INT (one) DEFAULT 0), 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

CREATE TRIGGER trigger_student_count_insertafter Inserton student_info for each rowupdate student_count SET student_ Count=student_count+1; CREATE TRIGGER trigger_student_count_deleteafter Deleteon student_info for each rowupdate student_count SET student_ Count=student_count-1;

<3> INSERT, delete data to see if the trigger is working correctly

Mysql> INSERT into Student_info VALUES (null, ' Zhang Ming '), (null, ' Liming '), (null, ' Wang Ming ')); Query OK,3 Rows Affected (0.02sec) Records:3 duplicates:0 warnings:0MySQL> SELECT *From Student_info;+--------+----------+| Stu_no |      Stu_name |+--------+----------+| 1 |      Zhang Ming | | 2 |      Li Ming | | 3 | Wang Ming |+--------+----------+3 rows in Set (0.00sec) MySQL> SELECT *From Student_count;+---------------+|             Student_count |+---------------+| 3 |+---------------+1 row in Set (0.00sec) MySQL> DELETE from Student_info WHERE stu_name in (' Zhang Ming ', ' Li Ming '); Query OK,2 Rows Affected (0.00sec) MySQL> SELECT *From Student_info;+--------+----------+| Stu_no |      Stu_name |+--------+----------+| 3 | Wang Ming |+--------+----------+1 row in Set (0.00sec) MySQL> SELECT *From Student_count;+---------------+|             Student_count |+---------------+| 1 |+---------------+1 row in Set (0.00 sec)

You can see that the number of students follows the change, whether it be an insert or a delete student.

To 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     = {User | Current_User}]    TRIGGER trigger_name    trigger_time Trigger_eventon tbl_name for each rowbegintrigger_statementend;

Example 2, creating a trigger with multiple execution statements

Still follow the table in the example above, make the following changes to the Student_count table: Increase the Student_class field represents the number of students in a specific grade, where 0 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.

<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 grade is 0;

<2> Create a trigger, first increase the total number of students in insert, then determine the number of new students in the grade, and then add the corresponding grade of students:

DELIMITER $ $CREATE TRIGGER trigger_student_count_insertafter Inserton student_info for each rowbeginupdate Student_ Count SET student_count=student_count+1 WHERE student_class=0; UPDATE student_count SET student_count=student_count+1 WHERE student_class=  NEWstudent_class; end$ $DELIMITER;

<3> create a trigger to reduce the total number of students at Delete, and then determine the number of students who are deleted, and then reduce the total of students in the corresponding grade:

DELIMITER $ $CREATE TRIGGER trigger_student_count_deleteafter Deleteon student_info for each rowbeginupdate Student_ Count SET student_count=student_count-1 WHERE student_class=0; UPDATE student_count SET student_count=student_count-1 WHERE student_class= old  . Student_class; end$ $DELIMITER;

<4> Insert student information from several different grades into the student table to see if the trigger is working:

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.02sec) Records:6 duplicates:0 warnings:0MySQL> SELECT *From Student_info;+--------+----------+---------------+| Stu_no | Stu_name |      Student_class |+--------+----------+---------------+| 4 |             AAA |      1 | | 5 |             BBB |      1 | | 6 |             CCC |      2 | | 7 |             DDD |      2 | | 8 |             ABB |      1 | | 9 |             ACC | 1 |+--------+----------+---------------+6 rows in Set (0.00sec) MySQL> SELECT *From Student_count;+---------------+---------------+| Student_count |             Student_class |+---------------+---------------+|             6 |             0 | |             4 |             1 | |             2 | 2 |+---------------+---------------+3 rows in Set (0.00 sec)

As you can see, a total of 6 data are inserted, the total number of students is 6, 1 grade 4, 2 grade 2, trigger correctly executed.

<5> remove students from different grades from the student table to see if the trigger is working:

Mysql> DELETE from Student_info WHERE stu_name like ' A% '3 rows affected (0.02 sec) MySQL> SELECT * From Student_info; +--------+----------+---------------+| Stu_no | Stu_name | Student_class |+--------+----------+---------------+|      5 | BBB      |             1 | |      6 | CCC      |             2 | |      7 | DDD      |             2 |+--------+----------+---------------+3 rows in Set (0.00 sec) MySQL> SELECT * from student_count;
    +---------------+---------------+| Student_count | Student_class |+---------------+---------------+|             3 |             0 | |             1 |             1 | |             2 |             2 |+---------------+---------------+3 rows in Set (0.00 sec)

The student information is removed from the student table with the name beginning with a, and the quantity table changes as the student's information is deleted.

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, and are organized as follows:

DELIMITER

Use begin ... End structure, you can define a trigger that executes multiple sentences of SQL. In the BEGIN statement block, you can also use other syntax, such as conditional statements and loop statements. In MySQL, the semicolon ";" Marks the end of the SQL statement, but the ";" is used in the SQL statement to be executed by the trigger. As the closing tag to execute the SQL statement, you need to redefine the end identifier.

Redefine the end identifier using the delimiter keyword followed by a space and a redefined end identifier.

Note: Unlike other statements, this statement does not require the end marker to be added at the end of the statement, such as delimiter

OfForUseIsWillIsYesOfKnotBeamStandard character Heavy new ding is to redefine the existing end identifier as, however, at this point the ";" is added at the end by habit or neglect. That is, "DELIMITER.; " That?TheLanguageSentenceOfForUse change will number " ; " Then the function of the statement becomes the symbol ";” As the new end marker. New and old

New is valid when the trigger is an Insert event type, representing the data that is currently being inserted; In the same way, old is valid when the trigger type is the Delete event type, indicating the data that is currently being deleted.

As in the example above, you can use New.student_class in a trigger to get the grade value in the student information you are inserting, using Old.student_class to get the grade value in the student information you are deleting.

Usage restrictions for triggers

Official website "Trigger syntax and examples" http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html

<1> triggers can only be created on permanent tables and cannot be created on temporary tables;

<2> triggers cannot use a call statement to invoke a stored procedure that has a return value or uses dynamic SQL (stored procedures can be returned to the trigger return value using the out or inout parameter).

<3> triggers cannot use a statement segment that opens or ends a transaction, such as starting a transaction (start TRANSACTION), committing a transaction (commit), or rolling back a transaction (ROLLBACK), but rolling back to a savepoint (SavePoint is allowed, Because rolling back to the savepoint does not end the transaction);

<4> foreign keys do not activate triggers;

<5> When you use row-based replication, the triggers from the table are not activated by manipulating the data in the main table. When you use statement-based replication, the triggers from the table are activated. Refer to section 17.4.1.34, "Replication and Triggers";

The return value is not allowed in the <6> trigger, so there is no return statement in the trigger, and if you want to stop a trigger immediately, you should use the Leave statement;

The exception mechanism in triggers

MySQL triggers are executed in the order of before triggers, row operations, after triggers, and any step in which an error occurs does not continue with the rest of the operation. If it is an operation on a transaction table, it will be rolled back as a transaction, but if it is an operation on a non-transactional table, the updated records will not be rolled back, which is also an issue to be aware of when designing the trigger.

View triggers

You can view the trigger by executing the show triggers command, but because you cannot query the specified trigger, it is inconvenient to return all the information for the trigger each time. However, you can use the Query system table Information_schema.triggers to specify the query criteria to view the specified trigger information. Such as:

mysql> use information_schema;database changedmysql> SELECT  * from triggers WHERE trigger_name= ' Trigger_student_count_insert ';

Delete Trigger

DROP TRIGGER trigger_name;

Summary of creation and use of mysql--triggers