怎么做两个表之间的触发器

以eschop的商品表，跟订单表为例：
新建商品表
create table goods(
    id int auto_increment primary key, #商品id
    name varchar(30) not null default '',#商品名
    num tinyint not null default 0    #商品数量
)engine  myisam default charset utf8;
新建订单变
create table indent(
    oid int auto_increment primary key,    #订单id
    gid int not null default 0,    #商品id
    much tinyint not null default 0    #购买数量
)engine myisam default charset utf8;
mysql> desc goods;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | NO   |     |         |                |
| num   | tinyint(4)  | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set
mysql> desc indent;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| oid   | int(11)    | NO   | PRI | NULL    | auto_increment |
| gid   | int(11)    | NO   |     | 0       |                |
| much  | tinyint(4) | NO   |     | 0       |                |
+-------+------------+------+-----+---------+----------------+
插入演示数据:
insert into goods values(1,'三星手机',12),(2,'ipad电脑',19),(3,'摩托罗拉mp3',38);
mysql> select * from goods;
+----+-------------+-----+
| id | name        | num |
+----+-------------+-----+
|  1 | 三星手机    |  12 |
|  2 | ipad电脑    |  19 |
|  3 | 摩托罗拉mp3 |  38 |
+----+-------------+-----+
手工给订单表添加购买记录:
insert into indent(gid,much)values(3,2);
mysql> select * from indent;
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
|   1 |   3 |    2 |
+-----+-----+------+
1 row in set
手工给商品表减少商品信息:
update goods set num=num-2 where id=3;
mysql> select * from goods;
+----+-------------+-----+
| id | name        | num |
+----+-------------+-----+
|  1 | 三星手机    |  12 |
|  2 | ipad电脑    |  19 |
|  3 | 摩托罗拉mp3 |  36 |
+----+-------------+-----+
3 rows in set
修改mysql的结束符:
mysql> delimiter $
-------------------------------------------
创建触发器
create trigger tg1
after insert  #在插入之后触发
on indent
for each row  #固定写法
begin
update goods set num=num-1 where id=3;
end
$
------------------------------------------
模拟用户下订单流程
商品表:
+----+-------------+-----+
| id | name        | num |
+----+-------------+-----+
|  1 | 三星手机    |  12 |
|  2 | ipad电脑    |  19 |
|  3 | 摩托罗拉mp3 |  36 |
+----+-------------+-----+
订单表:
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
|   1 |   3 |    2 |
+-----+-----+------+
①下订单
insert into indent(gid,much)values(2,4)$
②查看订单表
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
|   1 |   3 |    2 |
|   2 |   2 |    4 |
+-----+-----+------+
③商品表应该减少
+----+-------------+-----+
| id | name        | num |
+----+-------------+-----+
|  1 | 三星手机    |  12 |
|  2 | ipad电脑    |  19 |
|  3 | 摩托罗拉mp3 |  35 |
+----+-------------+-----+
结论:显然用户下了2号订单，下了4件商品，订单生成了！商品却没减少，还出现了错误！
-----------------------------------------
正确的创建触发器:
create trigger tg2
after insert
on indent
for each row
begin
update goods set num=num-new.much where id=new.gid;
end
$
-----------------------------------下订单insert触发器-----------------------------------------

出现错误：因为一张表不能同时被2个触发器监视，所以要删除开始创建的触发器
mysql> drop  tg1$
Query OK, 0 rows affected

mysql> show triggers$
Empty set
开始购买商品(清空订单表)：
mysql> select * from goods;
    -> $
+----+-------------+-----+
| id | name        | num |
+----+-------------+-----+
|  1 | 三星手机    |  12 |
|  2 | ipad电脑    |  19 |
|  3 | 摩托罗拉mp3 |  35 |
+----+-------------+-----+
3 rows in set

mysql> insert into indent(gid,much)values(2,4)$
Query OK, 1 row affected

mysql> select * from indent$    #下订单成功
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
|   1 |   2 |    4 |
+-----+-----+------+
1 row in set

mysql> select * from goods$    #对应商品自动减少OK
+----+-------------+-----+
| id | name        | num |
+----+-------------+-----+
|  1 | 三星手机    |  12 |
|  2 | ipad电脑    |  15 |
|  3 | 摩托罗拉mp3 |  35 |
+----+-------------+-----+
3 rows in set
------------------------------------------取消订单delete触发器------------------------------------
create trigger tg3
after delete
on indent
for each row
begin
update goods set num=num+old.much where id=old.gid;
end
$
注：真项目中，永远不会物理删除订单
----------------------------------
模拟取消订单：
mysql> select * from goods$
+----+-------------+-----+
| id | name        | num |
+----+-------------+-----+
|  1 | 三星手机    |  12 |
|  2 | ipad电脑    |  15 |
|  3 | 摩托罗拉mp3 |  35 |
+----+-------------+-----+
3 rows in set

mysql> select * from indent$
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
|   1 |   2 |    4 |
+-----+-----+------+
1 row in set

mysql> delete from indent where oid=1$
Query OK, 1 row affected

mysql> select * from indent$
Empty set

mysql> select * from goods$
+----+-------------+-----+
| id | name        | num |
+----+-------------+-----+
|  1 | 三星手机    |  12 |
|  2 | ipad电脑    |  19 |
|  3 | 摩托罗拉mp3 |  35 |
+----+-------------+-----+
3 rows in set
------------------------------------修改订单update触发器-----------------------------------------
修改订单公式:update goods set num=num+old.much-new.much where id=old.gid;
关键部分：新数量等=本身数量+被修改的旧数量-新产生的数量(完全数学逻辑)，id不变
create trigger tg4
after update
on indent
for each row
begin
update goods set num=num+old.much-new.much where id=old.gid;
end
$
-------------------------
mysql> select  * from goods$
+----+-------------+-----+
| id | name        | num |
+----+-------------+-----+
|  1 | 三星手机    |   7 |
|  2 | ipad电脑    |  19 |
|  3 | 摩托罗拉mp3 |  35 |
+----+-------------+-----+
3 rows in set

mysql> select  * from indent
$
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
|   2 |   1 |    5 |
+-----+-----+------+
1 row in set
mysql> update indent set much=10 where oid=2$
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select  * from goods$
+----+-------------+-----+
| id | name        | num |
+----+-------------+-----+
|  1 | 三星手机    |   2 |
|  2 | ipad电脑    |  19 |
|  3 | 摩托罗拉mp3 |  35 |
+----+-------------+-----+
3 rows in set

mysql> select  * from indent$
+-----+-----+------+
| oid | gid | much |
+-----+-----+------+
|   2 |   1 |   10 |
+-----+-----+------+
1 row in set
-------------------------------------------
触发器基础完成!

快速清空表:truncate [表名]
修改mysql的结束符:delimiter $;
显示触发器:show triggers
删除触发器:drop trigger [触发器名称]
创建触发器:
create trigger [触发器名称]
after [触发行为/insert/update/delete]
on [监视对象/某张表]
for each row #固定写法
begin
    sql语句;
end
$
注意:一个触发器只能对应某张表的某一个行为！不能多个触发器来监视某一张表的同一个行为！