Small shell _mysql Trigger use

Trigger Brief
1, Trigger basic concept
2. Trigger syntax and practical examples
3, before and after difference

first, the basic concept of triggers

1. Triggered

2. Role: Monitor a situation and trigger an action

3. Observe the scene

An e-shop:

Product list, goods

Primary KEY (GOODS_ID)	Product Name (Goods_name)	Stock (Goods_number)
1	Iphone6	10
2	Xiaomi phone	28

order form, orders

Order primary KEY (order_id)	Commodity primary KEY (goods_id)	Purchase quantity (Buy_num)
1	2	3
2	1	4

From the point of view of PHP, the logic of completing orders and reducing inventory is as follows:

A, after the order, insert the data into the Orders table:

INSERT into orders (Goods_id,bug_num) values (2,3);

b, modify the inventory of goods corresponding products:

Update goods setgoods_number=goods_number-3 where goods_id=2;

Summary: These two logic can be considered as a whole, or,insert-> trigger update

Processing scenarios: Using triggers to solve the above problem, we can monitor the change of a table, when a change occurs, trigger an action

4. Trigger monitoring and what changes are triggered

A, monitoring update/insert/delete

B, Trigger Update/insert/delete

second, trigger syntax

1. Create four elements of a grammar

2. Create a Trigger

2.1, the table structure of the test case

#商品表 CREATE TABLE Goods (goods_id int,goods_name varchar (ten), Goods_number smallint) Charset=utf8; INSERT INTO goods VALUES (1, ' iphone6 ', 10), (2, ' Xiaomi phone ', 28);

#订单表 CREATE TABLE Orders (order_id int,goods_id int,buy_num smallint) Charset=utf8; INSERT INTO Orders Values (2,1,4);

2.2. Create TRIGGER syntax

Createtrigger triggername

After/before Insert/update/delete on Table

Begin

SQL statement ( one sentence or multiple SQL)

End

2.3. Trigger Instance

2.3.1, set up triggers, update inventory on goods of the commodity table when inserting data to Orders table orders

Before inserting data into the Orders table, the data for each table is as follows

Insert data into the Orders table:

To view the data for the goods table:

At this point, the Orders table is inserted in Iphone6 two, but the decrease is the inventory of Millet mobile phone products.

When you insert a Xiaomi phone into the Orders table, two units?

At this time, the reduction is also Xiaomi phone, but the reduction is 1, not 2

Question: Where is it?

1 , view the trigger information you just created

The originally triggered SQL statement is fixed with an item ID, so the trigger only works on that item ID

2. How do I reference the value of a row in a trigger?

For insert, the new row, represented by new, is the value of each column in the row, represented by the new column name.

3 , modify trigger T1 ( Delete original re-established)

4 , validation results

Before inserting, each table data is as follows

Insert Orders table, IPhone6 three

Insert Orders table, Xiaomi phone five

2.3.2, set up triggers, update inventory on goods of commodity table when deleting data to Orders table orders

a , set up trigger T2

B. Delete Orders ' data

B1 , before deletion, the data of each table

B2 , after deletion, each table data:

Summarize:

For delete, how do I reference the value of a row in a trigger?

For a delete operation, the data it is working on is already present in the table, so the value of each column in the row is represented by the old. Column name.

2.3.3, set up triggers, update the inventory of the Commodity table goods when modifying data to Orders table orders

a , creating a trigger T3

B. Modify the Orders table data

B1 , before modification, the data of each table

B2 , modified, data for each table

Summarize:

For update, how do I reference the value of a row in a trigger?

For the update operation, the data it is going to manipulate is already in the table, so it uses old to refer to the pre-modified value, and the new value is used to refer to it after the modification.

2.4. View triggers and delete trigger syntax

2.4.1, view all triggers

2.4.2, viewing a trigger

2.4.3, deleting triggers

Three, the difference between before and after

1. Difference:

A, after is the first to complete the deletion of data after the change, and then trigger

Trigger in the statement later than additions and deletions, can not affect the previous additions and deletions to change the action

B, before is the first to complete the trigger, and then increase the deletion

The trigger statement takes place before the additions and deletions of the watch, and we have the opportunity to judge and modify the action that is about to occur.

2, Case:

To determine the order, if the order data >5, it is considered an illegal order, forcing the product data to be changed to 5

2.1. Setting up triggers

2.2, before inserting, the data of each table

2.3, after inserting, the data of each table

The quieter become,the more you is able to hear!

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Small shell _mysql Trigger use