Mysql --- triggers

TriggerMonitors a situation and triggers an operation when a situation occurs.

Application scenarios:

1. During online shopping, when a product order is submitted, a new record is inserted into the Order table, triggering a corresponding reduction in the inventory of the corresponding product table.

2. The verification card balance is triggered when the payment is confirmed during the payment process.

And so on.

The following uses online shopping as an example to describe the role of a trigger:

Existing item table (goods), including item id (goods_id), Item Name (goods_name), inventory quantity (goods_name)

There is also an order table (order_table), including the item id (goods_id), the number of purchases (buy_num)

If a row of records is inserted into the Order table

Indicates that the user has purchased 20 products with the id of 1, that is, 20 Xiaomi mobile phones.

At the same time, the inventory of Xiaomi mobile phones in the goods table should be reduced by 20.

The two steps are actually a whole, but we are doing it manually now. How can we make the second step automatically after the first step is completed?

The trigger concept can be used.The execution of the first insert statement triggers the second SQL statement to modify the inventory.L

Four elements of a trigger:

Monitoring location, monitoring event, trigger time, trigger event

Monitored location:The trigger monitoring object isTable

Monitoring event:The event monitored by the trigger can only beAdd, delete, and modify

Trigger time:When monitoring eventsBeforeOrAfter

Trigger event:It can only beAdd, delete, and modify
 

View existing triggers:

Show triggers;

Trigger creation:

Create trigger name
After/befor (trigger time)
Insert/update/delete (event monitoring)
On Table Name (Monitoring address)
For each row
Begin
Sql1;
..
SqlN;
End

Note that all the SQL statements to be triggered must end. There must also be a separator after the end, but it cannot be ';'. Therefore, you must modify the delimiters before creating a trigger.

Delimiter

Now we can use the trigger to modify the above example.

This trigger is completed: When 20 items with the id of 1 are ordered, the inventory of the goods table is reduced by 20 at the same time. Let's take a look at the effect.

The inventory of goods with the id of 1 is indeed reduced by 20. The trigger does complete the task, but this trigger is not intelligent. It can only handle the situation where the product id is 1 and the number of purchases is 20. To cope with other situations, you must obtain the order submission record.

Trigger reference row variable:

For insert, a new line is displayed after a row is inserted, which is represented by new. In the trigger, you can reference the values of each field in the inserted row through the new. Column name.

For delete, the row before deleting a row disappears and is represented by old. In the trigger, you can use the old. Column name to reference the values of fields in the deleted row.

For update, the old one after a row is modified is represented by the old one, and the new one is represented by the new one. In the trigger, you can use the new. column name to reference the values of each field after modification, old. column name to reference the values of each field before modification.

After knowing this, make further modifications to the trigger.

Let's look at the effect.

No commodity table before order submission

Submit two orders

Item table changes

It can be seen that the trigger does take effect.

Differences between trigger after and before:

After is triggered after addition, deletion, and modification are completed.

Before is determined before addition, deletion, and modification.

We have discussed the situation of "after" before. Now, if you submit an order to purchase 100 products with id 4, but Samsung has only 30 mobile phones in stock. If you follow the trigger above, the inventory will change to-70. This is obviously incorrect, so before can be used to judge the input at this time. If the purchased quantity is greater than the inventory, only the remaining items can be purchased.

Buy 100 products with id 4

Let's look at the Order table.

The actual purchase is only 30 instead of 100.