MySQL Learning Note (12) triggers

Learning content:

1. Trigger:

What is a trigger? When can we use triggers?

Triggers are used to listen for changes in a table and trigger an action when the table changes. Marshal says two tables are interrelated, and when we operate on one of the tables, and the data in the other table needs to do something, we need to create a trigger to listen to any change in the table, and when the data in one of the tables changes, The code block in the trigger will perform some sort of operation on the data for the other table ...

All in all, triggers are generally used between tables, and a single table creates triggers without any meaning ....

Trigger monitoring range: increase, delete, change

Triggered actions: increase, delete, change

A little ignorant, let's give an example ... For example, a ordering system. There is a database of order quantities, a takeaway-style database. When a customer makes a reservation, the quantity of the order changes, and the information about the takeout style database changes, and the two tables are interrelated. We can not get the quantity of out-of-stock takeout, we are still able to accept the order? Once this happens, there must be a problem ... So we can use triggers to solve this problem ...

Create TableOrder_form (order_form_idint  not NULL,//ID Order_form_much for each takeawayint  not NULL     //The maximum quantity allowed for each kind of takeaway); //Create an order form .....Create TableFood (food_idint  not NULL,//ID information for takeaway food_namenvarchar( -) not NULL,//Takeout name, style Food_muchint  not NULL              //The amount of stock sold out);Insert  intoOrder_formValues(1,Ten),(2,5),(3, the),(4, -),(5,5);//Inserting data InformationInsert  intoFoodValues(1,'Australian abalone',Ten),(2,'Lobster',5),(3,'shark fin', the),(4,'Flower Branches', -),(5,'Scallops',5);//Insert information For example, the user ordered an Australian abalone, then the food in the name of Australian abalone in the inventory quantity needs to be reduced by one, and the order of the name of the Australian abalone also need to reduce the number of ... When the maximum number is reduced to 0, then the inventory is empty, then the customer will no longer accept orders .... The implementation of this process requires a trigger ... Basic syntax:Create TriggerTrigger_name (after/BeforeInsert/Update/DeleteRange of monitoring) ontable_name forEach rowbeginone sentence or more SQL statements;End;Create TriggerTrg1 after onOrder_form forEach rowbegin       UpdateFoodSetFood_much=Food_much-1 wherefood_id=old.order_form_id;//This old must be added, otherwise the error ....End;UpdateOrder_formSetOrder_form_much=Order_form_much-1 whereorder_form_id=1;//when this sentence executes, the trigger will be triggered. It means that the customer has ordered a takeaway, then the number of orders at the same time, the inventory volume is also reduced .... This trigger is not flexible because it is not possible for customers to order a takeaway every time. It is possible to order multiple copies ... We can judge the quantity ordered, but if we order 1000 copies, we can't judge it 1000 times. So we can also create a new table that holds how many times each takeaway has been ordered. You can then modify the other two table information by saving the table information ....Create TableC_count (IDint  not NULL, Numint  not NULL default 0,    Primary Key(ID);), we update the order data to this form when there is an order. Build Trigger ... Create TriggerTrg2 after on Update  on  forEach rowbegin       UpdateFoodSetFood_much=Food_much-Old.numwherefood_id=old.id; UpdateFoodSetOrder_form_much=Order_form_much-Old.numwhereorder_form_id=old.id;End;UpdateC_countSetNum=2 whereId=3;//Trigger is triggered ...

The above is just a brief introduction of the update operation, I would like to briefly say how the insertion and deletion operation is triggered listening, although the mode is similar, but there are some areas to be aware of ...

Triggers after the delete operation:

For example, when the user cancels the order, then our corresponding allowed order quantity and the inventory quantity also must change with it ...

Create TableC_count (IDint  not NULL, Numint  not NULL default 0,    Primary Key(ID););//or the form ... Create TriggerTrg2 after on Update  on  forEach rowbegin       UpdateFoodSetFood_much=Food_much+Old.numwherefood_id=old.id; UpdateFoodSetOrder_form_much=Order_form_much+Old.numwhereorder_form_id=old.id; also uses (old. Data) to represent the data information before the deleteEnd;Delete  fromC_countwhereId=1;

Trigger for insert operation:

For example, a new product has been added in the store ... Then we still need to update the information in the database ...

Mysql> Create TriggerTrg1 afterInsert  onC_count forEach row - begin      -     Insert  intoFoodValues(6,'Halibut',8);  -     Insert  intoOrder_formValues(6,8);  - End; MySQL> Insert  intoC_countValues(6,8); //Here's another note: We need to use (old. Data) to manipulate the data before we delete it ... So here's the insert operation, we need to use (new data) to manipulate the new data we inserted ...

To delete a trigger:

Drop Trigger Trg1;

To view trigger information:

Show triggers;

The difference between after and before in triggers:
After is the first to complete the data increase, delete, change the trigger
Trigger the statement is later than the monitoring of the increase, delete, change, can not affect the previous additions and deletions to change the action
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.

MySQL Learning Note (12) triggers