Introduction and use cases of triggers in Mysql _ MySQL

Introduction to triggers in Mysql and use cases bitsCN.com 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; bitsCN.com