MySQL (11) Trigger

The previous article is a relatively simple view, in fact, it is relatively simple to use, and later what more about the use of views, the time to add in their own. Next, let's look at the use of triggers!

I. Overview OF triggers 　 1.1. What is a trigger

Trigger (Trigger): Monitors a situation and triggers an action. In MySQL server is a certain operation on a table, triggering a certain condition (insert,update,delete, etc.), which automatically executes a program .

Note: You must have quite a lot of permissions to create a trigger (created TRIGGER), which is sufficient if you are already a root user. This is different from the SQL standard.

　 1.2. Trigger Action

So why use database object triggers? In a specific development project, you will often encounter the following examples:
1) in the student table, you have the field student's name, the total number of fields, and 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 telephone, 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 .

　 1.3, trigger four elements

　　Watch Location: Table
　　Listener Event: Insert/update/delete
　　Trigger Time: After/before
　　Trigger Event: Insert/update/delete

second, trigger usage2.1. Trigger Syntax

　　 CREATE TRIGGER < trigger name > --the trigger must have a name, a maximum of 64 characters, and may be appended with a delimiter. It is basically like naming other objects in MySQL.
{ before | After } --the trigger has an execution time setting: It can be set before or after the event occurs.
{ INSERT | UPDATE | DELETE} -You can also set triggered events: they can be triggered during INSERT, update, or delete execution.
On < table name > --triggers are part of a table: triggers are activated when an INSERT, update, or delete operation is performed on the table. We cannot schedule two triggers for the same event in the same table.
for eachROW -The execution interval of the trigger: the For each row clause notifies the trigger to perform an action every other row, rather than once for the entire table.
< trigger SQL statement > --The trigger contains the SQL statement you want to trigger: The statement here can be any legitimate statement, including compound statements, but the statements here are constrained by the same limitations as functions.

The simple writing is this:

After/before insert/update/  Delete on table 　　  for　　Each row # # # #这句话在MySQL中是固定的 begin SQL statement; end$

Parsing: Because of the "SQL statement in the preceding code snippet;" is terminated with a semicolon, so you need to replace the end sign in MySQL with "$", the command to change the end of the MySQL command: delimiter $;
The color of each segment in the trigger syntax above is viewed with the four-factor counterpart.

　 2.2. Create a trigger

1) Create two tables

int PRIMARY KEY auto_increment,name varchar (+int int int int);

2) inserting data into the product table

Insert into Tb_goods (name,num) VALUES (' product 1',' 2' ,ten), (' merchandise 3',ten);

3) If we sell 3 items 1

No triggers:

Insert a record into the order table: INSERT into Tb_orders (Good_id,much) values (1,3);
Update the remaining quantity of item 1 in the Product list: Update tb_goods set num=num-3 where id=1;

To create a trigger:

　　Create trigger tg_1 after insert on tb_orders
For each row
Begin
Update tb_goods set num=num-3;
end$

This time if you execute INSERT into tb_orders (Good_id,much) values (1,3), you will find that the number of items has changed to 7, indicating that the trigger automatically updates when an order is inserted.

　 2.3, trigger on the worthy reference

The trigger has a problem because Num and ID are written in the trigger, so regardless of which product is purchased, the final update is the number of items 1. At this point, you need to change the value in the trigger to dynamic fetch.

　　for INSERT, the newly inserted row is represented by new, and the value of each column in the row is denoted by "new. Column Name" :

To create a new trigger that dynamically gets the value:

CREATE trigger tg_2 after insert on Tb_orders  for　　 Set num=num-newwhere id=new. ID; end$

Delete the first trigger: Drop trigger tg_1;
Insert an Order record: INSERT INTO tb_orders (Good_id,much) VALUES (2,3) $
The number of items found after execution is 7, which is appropriate.

third, the trigger instance

1) Create a table Tab1

DROP TABLE IF EXISTS tab1; CREATE TABLE tab1 (    tab1_id varchar (one));

2) Create a table tab2

DROP TABLE IF EXISTS tab2; CREATE TABLE tab2 (    tab2_id varchar (one));

3) Create trigger:t_afterinsert_on_tab1　　

DROP TRIGGER IF EXISTS t_afterinsert_on_tab1; CREATE TRIGGER t_afterinsert_on_tab1 After insert in Tab1for each rowbegin     INSERT into TAB2 (tab2_id) VALUES (  Newtab1_id); END;

4) want to TAB1 insert data

INSERT into TAB1 (tab1_id) VALUES ('0001');

5) View Changes

SELECT ** from TAB2;

MySQL (11) Trigger