mysql触发器触发hive_mysql触发器 - osc_e4hg9m7f的个人空间 - OSCHINA - 中文开源技术交流社区...

创建商品表：

Database changed

mysql> create table goods(

-> gid int(10),

-> name varchar(20),

-> num smallint

-> );

Query OK, 0 rows affected (0.02 sec)

创建订单表：

mysql> create table ord(

-> ord int(10),

-> gid int(10),

-> mch smallint,

-> );

插入测试数据：

insert into goods values(1,'cat',34),(2,'dog',65),(3,'pig',21);

mysql> select * from goods;

+------+------+------+

| gid | name | num |

+------+------+------+

| 1 | cat | 34 |

| 2 | dog | 65 |

| 3 | pig | 21 |

+------+------+------+

创建触发器：

//修改mysql的结束符号为$,因为select语句要用分号

delimiter $

mysql> create trigger t1 after insert on ord for each

-> row begin update goods set num = num-2 where gid = 1;

-> end $

Query OK, 0 rows affected (0.02 sec)

查看触发器：

mysql> show triggers;

-> $

现在操作ord数据添加的时候，goods表的数据会跟着减少了