What is a trigger?
A trigger is a database program that monitors a behavior of a data table. Once the behavior of a data table occurs, the corresponding SQL statement is executed immediately.
Syntax structure of the trigger:
Name of the create trigger: name of the table on which the trigger event is listened for each row
Trigger event composition:; consists of two parts:
Trigger event occurrence time ----- is the behavior of the monitored table after before is commonly used after
Trigger execution content: add, delete, modify
When creating an order table, note that order is sorted by a keyword in mysql. To avoid errors, we can add backquotes to indicate that this is not a keyword.
Case study:
Once an order is generated, the corresponding inventory table must be subtracted from the corresponding data.
(1) create two tables: one commodity goods table and one order table.
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',), (null, 'iphone4s ', 3
0), (null, 'lnmia ', 40), (null, 'samsung', 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 a trigger
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 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: cancel the previous order and 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;