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

接上个问题

number 和 gid 都传什么？

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

insert 和 delete  两个状态

delimiter $
create trigger t2
after 
insert
on ordz
for each row
begin 
	update goods set number = number-? where gid = ?;  # 哪一个？ 因为是新的所以字段为 new
end $

改为

delimiter $
create trigger t2
after 
insert
on ordz
for each row
begin 
	update goods set number = number-new.much where gid = new.gid;  # new
end $

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 t3
after 
delete
on ordz
for each row
begin
	update 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'
	三种状态

比如改pig
mysql> 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 t4
before
update
on ordz
for each row 
begin 
	update goods set number = number + old.much - new.much where gid = old.gid;
									# +(5-3) = 2
end$

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=4
ordz:
	+-----+------+------+
	| 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  就体现出来了

爆仓了！！！