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

以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 setmysql> 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 tg1after insert  #在插入之后触发on indentfor each row  #固定写法beginupdate 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 tg2after inserton indentfor each rowbeginupdate goods set num=num-new.much where id=new.gid;end$-----------------------------------下订单insert触发器-----------------------------------------出现错误：因为一张表不能同时被2个触发器监视，所以要删除开始创建的触发器mysql> drop  tg1$Query OK, 0 rows affectedmysql> show triggers$Empty set开始购买商品(清空订单表)：mysql> select * from goods;    -> $+----+-------------+-----+| id | name        | num |+----+-------------+-----+|  1 | 三星手机    |  12 ||  2 | ipad电脑    |  19 ||  3 | 摩托罗拉mp3 |  35 |+----+-------------+-----+3 rows in setmysql> insert into indent(gid,much)values(2,4)$Query OK, 1 row affectedmysql> select * from indent$    #下订单成功+-----+-----+------+| oid | gid | much |+-----+-----+------+|   1 |   2 |    4 |+-----+-----+------+1 row in setmysql> select * from goods$    #对应商品自动减少OK+----+-------------+-----+| id | name        | num |+----+-------------+-----+|  1 | 三星手机    |  12 ||  2 | ipad电脑    |  15 ||  3 | 摩托罗拉mp3 |  35 |+----+-------------+-----+3 rows in set------------------------------------------取消订单delete触发器------------------------------------create trigger tg3after deleteon indentfor each rowbeginupdate 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 setmysql> select * from indent$+-----+-----+------+| oid | gid | much |+-----+-----+------+|   1 |   2 |    4 |+-----+-----+------+1 row in setmysql> delete from indent where oid=1$Query OK, 1 row affectedmysql> select * from indent$Empty setmysql> 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 tg4after updateon indentfor each rowbeginupdate 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 setmysql> select  * from indent$+-----+-----+------+| oid | gid | much |+-----+-----+------+|   2 |   1 |    5 |+-----+-----+------+1 row in setmysql> update indent set much=10 where oid=2$Query OK, 1 row affectedRows matched: 1  Changed: 1  Warnings: 0mysql> select  * from goods$+----+-------------+-----+| id | name        | num |+----+-------------+-----+|  1 | 三星手机    |   2 ||  2 | ipad电脑    |  19 ||  3 | 摩托罗拉mp3 |  35 |+----+-------------+-----+3 rows in setmysql> 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$注意:一个触发器只能对应某张表的某一个行为！不能多个触发器来监视某一张表的同一个行为！