Beckham _ mysql trigger and Beckham _ mysql trigger

Beckham _ mysql trigger and Beckham _ mysql trigger
TriggerBrief
1. Basic concepts of triggers
2. Trigger syntax and practical examples
3. Differences between before and after

 

I. Basic concepts of triggers

1. Instant Transmission

2. Role: monitor a situation and trigger an operation

3. Observation scenarios

An electronic MALL:

Item table, goods

Primary Key (goods_id)	Item Name (goods_name)	Inventory (goods_number)
1	IPhone 6	10
2	Xiaomi mobile phone	28

 

Order table, orders

Order primary key (order_id)	Item primary key (goods_id)	Purchase quantity (buy_num)
1	2	3
2	1	4

From the php perspective, the logic for placing an order and reducing inventory is as follows:

A. insert data to the orders table after placing an order:

Insert into orders (goods_id, bug_num) values (2, 3 );

B. Modify the inventory of goods corresponding to goods:

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

Conclusion: These two logics can be regarded as a whole, or insert-> trigger update.

Solution: Use a trigger to solve the above problem. We can monitor the changes of a table and trigger an operation when a change occurs.

4. Trigger monitoring and trigger changes

A. Monitor update/insert/delete

B. Trigger update/insert/delete

Ii. Trigger syntax

1. Four Elements of syntax Creation

2. Create a trigger

2.1. Table Structure of Test Cases

# Commodity table Create table goods (Goods_id int, goods_name varchar (10), goods_number smallint) charset = utf8;   Insert into goods Values (1, 'iphone6', 10), (2, 'xiaomi phone', 28 );

# Order table Create table orders (Order_id int, goods_id int, buy_num smallint) charset = utf8;   Insert into orders Values (1, 2, 3), (2, 1, 4 );

2.2 create trigger syntax

Createtrigger triggerName

After/beforeinsert/update/deleteon Table

Begin

SQL statement (one or more SQL statements)

End;

2.3 trigger instance

2.3.1 create a trigger to update the inventory of the goods table when inserting data into the Order table orders

Before inserting data into the orders table, the data of each table is as follows:

Insert data to the orders table:

View the data in the goods table:

At this time, the orders table is inserted with two iphone6 instances, but the inventory of Xiaomi mobile products is reduced.

What if two Xiaomi mobile phones are inserted into the orders table?

At this time, Xiaomi's cell phone is reduced, but 1 instead of 2.

Question: Where is it?

1. view the created trigger information

The original SQL statement is fixed for a commodity id. Therefore, the trigger only applies to the commodity id.

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

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

3. Modify trigger t1 (delete the original rebuild)

4. Verification results

Before insertion, the data in each table is as follows:

Insert the orders table, iPhone 6, three

Insert the orders table, five Xiaomi mobile phones

 

2.3.2 create a trigger to update the inventory of the goods table When deleting data from the order table orders

A. Create trigger t2

B. Delete orders data

B1. Table data before deletion

B2. Data in each table after deletion:

Summary:

For delete, how does one reference the row value in the trigger?

For the delete operation, the data to be operated already exists in the Table. Therefore, it is represented by old. The values of each column in the row are represented by the old. Column name.

 

2.3.3 create a trigger to update the inventory of goods in the product table when modifying data in the order table orders

A. Create trigger t3

B. modify orders table data

B1. Data of each table before modification

B2. Data of each table after modification

 

Summary:

For update, how does one reference the value of a row in the trigger?

For the update operation, the data to be operated already exists in the table, so old is used to reference the value before modification, and new is used to reference the new value after modification.

 

2.4 view trigger and delete trigger syntax

2.4.1 view all triggers

2.4.2 view a trigger

2.4.3 delete a trigger

 

Iii. Differences between before and after

1. Differences:

A. after is triggered after adding, deleting, and modifying data.

The statement in the trigger is later than the addition, deletion, modification, and deletion actions cannot be affected.

B. before is triggered first, and then added and deleted

The triggered statement takes place before the added, deleted, and modified statement. We have the opportunity to determine whether to modify the forthcoming operation.

2. case:

Judge the order. If the data of the Order is greater than 5, the order is regarded as illegal and the data of the ordered goods is forcibly changed to 5.

2.1 create a trigger

2.2 data of each table before insertion

2.3 data of each table after insertion

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

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.