MySQL---triggers

A trigger (trigger) can, as the name implies , monitor a situation, triggering an action when the situation occurs

Application Scenarios:

1, during the online shopping process, when the product order is submitted, the new record is inserted into the order form, and the inventory of the corresponding commodity table is triggered to be reduced accordingly.

2. During the payment process, the balance on the verification card is triggered when the payment is confirmed.

Wait a minute

Use the online shopping situation to illustrate the role of triggers:

Existing commodity table (goods), including commodity ID (goods_id), trade name (goods_name), Stock quantity (Goods_name)

There is also the order form (order_table), including the Product ID (goods_id), purchase quantity (Buy_num)

If you insert a row of records into the order table

On behalf of the user purchased 20 commodity ID 1 commodity, namely purchased 20 Mi phone

At the same time, the logic of the goods table in the Millet phone inventory should also be subtracted from 20 units.

These two steps should actually be a whole, and we are now working manually. How do you get the first step done, and the second step is executed automatically?

This makes it possible to use the concept of triggers. The execution of the first INSERT statement triggers the second modified sq of inventory L

Trigger four elements:

Monitoring locations, monitoring events, triggering times, triggering events

Watch Location: The object that the trigger monitors is a table

Monitoring events: trigger monitoring events can only be added, deleted, changed

Trigger time: before or after the event is monitored

Trigger Event: can only be added, deleted, changed

To view the currently existing triggers:

Show triggers;

Creation of triggers:

CREATE TRIGGER trigger name
After/befor (Trigger Time)
Insert/update/delete (Monitoring events)
On table name (monitor address)
For each row
Begin
SQL1;
..
SQLN;
End

It is important to note that the SQL statements that need to be triggered end with '; '. End is also required to have a delimiter, but can no longer be '; '. So you need to modify the delimiter before creating the trigger.

Delimiter delimiter

Now use the trigger to modify the above example

This trigger is complete: when you order 20 items with a product ID of 1, the inventory for the goods table is synchronized minus 20. Let's take a look at the effect

The inventory for commodity ID 1 did subtract 20. The trigger does complete the task, but the trigger is not smart, and he can only handle the case where the product ID is 1 and the purchase quantity is 20. You need to get a record of the order to be submitted if you want to respond to other situations.

Triggers reference row variables:

For INSERT, a new line appears after a row is inserted, and the value of each field in the inserted row can be referenced in the trigger by new. Column name.

For delete, the row before deleting a row is missing, and old indicates that the value of each field in the deleted row can be referenced in the trigger.

For update, modify one row after the old one, the new line is represented in the, and in the trigger you can use the new column name to refer to the change the value of each field after the modification. The column name to refer to the values of the previous fields.

Once you know this, make further modifications to the trigger

And look at the effect.

Absence of pre-order product list

Submit two Orders

Commodity table Changes

A visible trigger does work

The difference between a trigger after and before:

After is the first to complete the deletion and re-trigger

Before is to be judged before adding or deleting.

Already discussed in the previous situation, now if submitted an order to purchase 100 ID 4 items, but Samsung mobile phone inventory only 30. If you follow the above trigger, the last inventory will become-70. This is obviously wrong, so this time you can use before to judge the input, if the purchase quantity is larger than the inventory can only buy the remaining goods.

Buy 100 items with ID 4 now

Look at the order form again.

The actual purchase is only 30 instead of 100.