mysql高级 tigger触发器 --[2]

来源：互联网发布：淘宝开店技巧与经验编辑：程序博客网时间：2024/05/23 14:14

接上个问题

number 和 gid 都传什么？

insert语句是否可以被引用到？

insert 和 delete 两个状态

delimiter $create trigger t2after inserton ordzfor each rowbegin update goods set number = number-? where gid = ?;  # 哪一个？ 因为是新的所以字段为 newend $

改为

delimiter $create trigger t2after inserton ordzfor each rowbegin update goods set number = number-new.much where gid = new.gid;  # newend $

ok，报错了

大致意思是不能同时监视两个一样的如 insert 这种操作！！

查看已经有的： show triggers [\G]

删掉已经有的： drop trigger Name;

再次运行就可~~~

truncate ordz;   # 不干扰视线

insert into ordz values(3,2,2)$insert into ordz values(4,3,9)$+-----+------+--------+| gid | name | number |+-----+------+--------+|   1 | cat  |     14 ||   2 | dog  |     18 ||   3 | pig  |     11 |+-----+------+--------+    # 成功  合理的删除了  改删除的

如果取消订单？应该恢复！

比如第三个pig商品

create trigger t3after deleteon ordzfor each rowbeginupdate goods set number = old.much+number where gid= old.gid;end$delete from ordz where oid = 4 $   # 删除订单 +-----+------+--------+| gid | name | number |+-----+------+--------+|   1 | cat  |     14 ||   2 | dog  |     18 ||   3 | pig  |     20 |+-----+------+--------+

改数量：

''   insert  'new''old'  delete  '''old'  update  'new'三种状态

比如改pigmysql> select * from goods;+-----+------+--------+| gid | name | number |+-----+------+--------+|   1 | cat  |     14 ||   2 | dog  |     18 ||   3 | pig  |     11 |+-----+------+--------+3 rows in set (0.00 sec)mysql> select * from ordz;$+-----+------+------+| oid | gid  | much |+-----+------+------+|   1 |    1 |    2 ||   3 |    2 |    2 ||   4 |    3 |    9 |+-----+------+------+

create trigger t4beforeupdateon ordzfor each row begin update goods set number = number + old.much - new.much where gid = old.gid;# +(5-3) = 2end$update ordz set much = 5 where oid=4$mysql> select * from goods$+-----+------+--------+| gid | name | number |+-----+------+--------+|   1 | cat  |     14 ||   2 | dog  |     18 ||   3 | pig  |     15 |+-----+------+--------+3 rows in set (0.00 sec)mysql> select * from ordz$+-----+------+------+| oid | gid  | much |+-----+------+------+|   1 |    1 |    2 ||   3 |    2 |    2 ||   4 |    3 |    5 |+-----+------+------+3 rows in set (0.00 sec)before  好像没有区别？！！~~如果剩余3头猪，但是客户买了10头猪，发生了什么情况！！  能否预防？

例：

> update ordz set much = 30 where oid=4ordz:+-----+------+------+| oid | gid  | much |+-----+------+------+|   1 |    1 |    2 ||   3 |    2 |    2 ||   4 |    3 |   30 |+-----+------+------+goods:+-----+------+--------+| gid | name | number |+-----+------+--------+|   1 | cat  |     14 ||   2 | dog  |     18 ||   3 | pig  |    -10 |+-----+------+--------+   # 能否在购买量超过库存时，把much自动改为number 。   before  就体现出来了

爆仓了！！！

0 0