mysql 触发器例子

mysql> use page
Database changed
mysql> show tables;
+----------------+
| Tables_in_page |
+----------------+
| bank           |
| book           |
+----------------+
2 rows in set (0.00 sec)

mysql> create table goods(id int primary key auto_increment,goods_name varchar(6
4),shop_price decimal(10,2),goods_number int)engine=myisam default charset=utf8;

Query OK, 0 rows affected (0.31 sec)

mysql> create table `order`(goods_id int primary key auto_increment,goods_name v
archar(64),buy_number int)engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.05 sec)

mysql> drop table goods;
Query OK, 0 rows affected (0.06 sec)

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=myisam default charset
=utf8;
Query OK, 0 rows affected (0.05 sec)

mysql> desc goods;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| goods_id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| goods_name   | varchar(64)   | YES  |     | NULL    |                |
| shop_price   | decimal(10,2) | YES  |     | NULL    |                |
| goods_number | int(11)       | YES  |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

mysql> desc `order`;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| goods_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| goods_name | varchar(64) | YES  |     | NULL    |                |
| buy_number | int(11)     | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert goods values(null,'nokiaN85',2000,35),(null,'iphone4s',4500,30),(n
ull,'Lumia',5000,40),(null,'samsung',4200,20);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | nokiaN85   |    2000.00 |           35 |
|        2 | iphone4s   |    4500.00 |           30 |
|        3 | Lumia      |    5000.00 |           40 |
|        4 | samsung    |    4200.00 |           20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)

mysql> create trigger alert_goods_number after insert on `order` for each row up
date goods set goods_number = goods_number-5 where goods_id=1;
Query OK, 0 rows affected (0.34 sec)

mysql> insert into `order` values(1,'nokiaN85',5);
Query OK, 1 row affected (0.34 sec)

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | nokiaN85   |    2000.00 |           30 |
|        2 | iphone4s   |    4500.00 |           30 |
|        3 | Lumia      |    5000.00 |           40 |
|        4 | samsung    |    4200.00 |           20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)

mysql> drop trigger alert_goods_number;
Query OK, 0 rows affected (0.00 sec)

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.g
oods_id;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into order values(4,'samsung',5);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'order
 values(4,'samsung',5)' at line 1
mysql> insert into `order` values(4,'samsung',5);
Query OK, 1 row affected (0.05 sec)

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | nokiaN85   |    2000.00 |           30 |
|        2 | iphone4s   |    4500.00 |           30 |
|        3 | Lumia      |    5000.00 |           40 |
|        4 | samsung    |    4200.00 |           15 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)

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;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from order;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'order
' at line 1
mysql> select * from `order`;
+----------+------------+------------+
| goods_id | goods_name | buy_number |
+----------+------------+------------+
|        1 | nokiaN85   |          5 |
|        4 | samsung    |          5 |
+----------+------------+------------+
2 rows in set (0.00 sec)

mysql> delete from `order` where goods_id=1;
Query OK, 1 row affected (0.00 sec)

mysql> delete from `order` where goods_id=4;
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | nokiaN85   |    2000.00 |           35 |
|        2 | iphone4s   |    4500.00 |           30 |
|        3 | Lumia      |    5000.00 |           40 |
|        4 | samsung    |    4200.00 |           20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> create trigger update_goods_number after update on `order` for each row u
pdate goods set goods_number = goods_number-new.buy_number+old.buy_number where
goods_id = new.goods_id;
Query OK, 0 rows affected (0.28 sec)

mysql> insert `order` values(1,'nokiaN85',5);
Query OK, 1 row affected (0.01 sec)

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | nokiaN85   |    2000.00 |           30 |
|        2 | iphone4s   |    4500.00 |           30 |
|        3 | Lumia      |    5000.00 |           40 |
|        4 | samsung    |    4200.00 |           20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)

mysql> select * from `order`;
+----------+------------+------------+
| goods_id | goods_name | buy_number |
+----------+------------+------------+
|        1 | nokiaN85   |          5 |
+----------+------------+------------+
1 row in set (0.00 sec)

mysql> update `order` set buy_number=10 where goods_id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from `order`;
+----------+------------+------------+
| goods_id | goods_name | buy_number |
+----------+------------+------------+
|        1 | nokiaN85   |         10 |
+----------+------------+------------+
1 row in set (0.00 sec)

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | nokiaN85   |    2000.00 |           25 |
|        2 | iphone4s   |    4500.00 |           30 |
|        3 | Lumia      |    5000.00 |           40 |
|        4 | samsung    |    4200.00 |           20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)