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

爆仓怎么办：

用sql的判断语句，就像某某语言一样！

/*之前一些语句：declare xx int/char。。   这个是声明变量if xxx then xyxyend if;    if语句*/create trigger t2after inserton ordzfor each rowbegin update goods set number = number-new.gid where gid = new.number;  // newend $-- t2基础上，完成much 和 num判断delimiter $create trigger t5after inserton ordz  for each rowbegin declare rnum int;# 判断select number into rnum from goods where gid = new.gid;if new.much > rnum then set new.much = rnum;end if;update goods set number = number-new.much where gid = new.gid;  end $#  报错：ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger   #  说是不能在语句after之后 在搞#  得在语句执行前面诶 before#  应该在生米煮成熟饭之前搞create trigger t5beforeinserton ordz  for each rowbegin declare rnum int;# 判断select number into rnum from goods where gid = new.gid;if new.much > rnum then set new.much = rnum;end if;update goods set number = number-new.much where gid = new.gid;  end $+-----+------+--------+| gid | name | number |+-----+------+--------+|   1 | cat  |     20 ||   2 | dog  |     20 ||   3 | pig  |     20 |+-----+------+--------+insert into ordz values(5,3,50); #  50头猪+-----+------+--------+| gid | name | number |+-----+------+--------+|   1 | cat  |     20 ||   2 | dog  |     20 ||   3 | pig  |      0 |+-----+------+--------++-----+------+------+| oid | gid  | much |+-----+------+------+|   5 |    3 |   20 |+-----+------+------+只是下了20头猪， 没有多余的啦~~~~

完成！  多看手册！！！

for each row 是什么？

行级触发器？

语句级触发器？

# 触发器的  for each row  是什么？触发器分为：语句级触发器， 和行级触发器比如create trigger tn after update on xxtable for each row begin sql N/end$执行：update  xx table set xx=xx where id > 100那么：sqlN/ 被触发几次？  以为id > 100 被触发 100 次------------------------------delimiter $create trigger t4before updateon ordzfor each row begin insert into tmp values (5);end $##  ERROR 1415 (0A000): Not allowed to return a result set from a trigger#   不允许 result 集合集create table tmp(id int)$select * from ordz $+-----+------+------+| oid | gid  | much |+-----+------+------+|   1 |    1 |    2 ||   3 |    2 |    2 ||   4 |    3 |   30 ||   5 |    3 |   20 |+-----+------+------+update ordz set much = 1 where oid = 5 $mysql> select * from tmp$+------+| id   |+------+|    5 |+------+update ordz set much = 3 $mysql> select * from tmp$+------+| id   |+------+|    5 ||    5 ||    5 ||    5 ||    5 |+------+影响多少行，每一行受影响， 触发器就执行，所以叫行级触发器！# 在oracle中如果不写， 那么无论update语句影响多少行，都只执行一次/*比如：1个人下了订单， 买了5件商品， insert 5 次，可以用行级触发器，修改5次库存用语句级来，insert一条发货提醒但是 -- mysql 暂时不支持 语句级的触发器for each row 是行级 ，mysql中必须有其他的数据库，如果不写，则默认为语句级的*/