A simple introduction to the triggers in Mysql and the use case _mysql

What is a trigger?

A trigger is a database program that listens to an action on a data table, and executes the SQL statement as soon as the behavior of the datasheet occurs.

syntax structure of triggers:

Create TRIGGER Trigger Name trigger event on listener table name for the SQL statement executed after the occurrence of each row behavior

A trigger event consists of two parts:

The trigger event occurs-----The behavior of the monitored table after before is commonly used after

What triggers do: additions and deletions

When creating an order table, be aware that because the order is a keyword sort in mysql, to avoid errors, we can add inverted quotes to indicate that this is not a keyword

Case Study:

Once the order is generated, the corresponding inventory table is subtracted from the corresponding data

(1) Build two tables: a Product goods table an order form
Mysql> CREATE TABLE goods (goods_id int primary key auto_increment,goods_name var
char (), Shop_price decimal (10,2), Goods_number int) Engine=mysiam default CharSet
=utf8;

Mysql> CREATE TABLE ' order ' (goods_id int primary key Auto_increment,goods_name V
Archar (), Buy_number int) Engine=mysiam default Charset=utf8;

Mysql> INSERT INTO goods values (null, ' nokiaN85 ', 2000,35), (null, ' iphone4s ', 4500,3
0), (null, ' Lnmia ', 5000,40), (null, ' Samsung ', 4200,20);

mysql> Select*from goods;
+----------+------------+------------+--------------+
| goods_id | Goods_name | Shop_price | Goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 35 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lnmia | 5000.00 | 40 |
| 4 | Samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+

(2) Creating triggers
Mysql> CREATE trigger Alter_goods_number after insert on ' order ' for each row up
Date goods set goods_number=goods_number-5 where goods_id=1;

mysql> insert INTO ' order ' values (1, ' nokiaN85 ', 5);

mysql> Select*from goods;
+----------+------------+------------+--------------+
| goods_id | Goods_name | Shop_price | Goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 30 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lnmia | 5000.00 | 40 |
| 4 | Samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+


New using mysql> create trigger Alter_goods_number after insert on ' order ' for each row up
Date goods set Goods_number=goods_number-new.buy_number where goods_id=new.goods
_id;

mysql> insert INTO ' order ' values (4, ' Samsung ', 5);

Use of old

Mysql> CREATE trigger Back_goods_number after delete on "order" for each row upd
Ate goods set Goods_number=goods_number+old.buy_number where Goods_id=old.goods_
Id

mysql> Delete from ' order ' where goods_id=1;

Update (update will be placed before the order is revoked, and then place the order again)

Mysql> CREATE trigger Update_goods_number after update on ' order ' for each row u
Pdate goods set Goods_number=goods_number+old.buy_number-new.buy_number where go
ods_id=new.goods_id;

mysql> Update ' order ' Set buy_number = 10;