Triggers in Mysql are briefly introduced and used in some cases

What is a trigger?

A trigger is a program in a database that listens for the behavior of a table. Once the behavior occurs, the corresponding sql statement is executed immediately

The syntactic structure of the trigger:

Name of create trigger trigger Trigger event on listens for table name for each row statement executed after the behavior occurs

Trigger events consist of:; Two parts:

Trigger event time is the behavior of the listening table after before after

What the trigger executes: Add, delete, or change

When creating the order table, it is important to note that order in mysql is a keyword sort. To avoid errors, we can add back quotes to indicate that this is not a keyword

Case Study:

Once an order is generated, the corresponding inventory table is subtracted with the corresponding data

(1) Two tables: goods for one commodity and order for one order
mysql > create table goods(goods_id int primary key auto_increment,goods_name var
char(64),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(64),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) Create 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 |
+----------+------------+------------+--------------+


Use of new 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);

The 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 cancels the previous order and places a new order)

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 ;