2 jack home jack_home 于 2013.10.11 12:19 提问

Mysql数据库触发器执行不成功

Mysql数据库中有order、orderitem和goods三张表,其中order与orderitem通过order的ID字段与orderitem的orderid字段关联;其中goods与orderitem通过goods的ID字段与orderitem的goodsid字段关联;现在想做一个触发器,当一个订单被确认的时候,该订单中所有产品的剩余数量和出售数量做相应的改变,order表isvalid字段的值有其他值变为Y的时候更新goods表中store和salecount值,请问写入下的触发器为什么不能实现目的呢,另外在脚本中提示已经创建成功怎么通过SELECT * FROM information_schema.TRIGGERS;查询不到触发器呢?

delimiter ||
drop trigger if exists updatestore||
create trigger updatestore after update on order for each row
begin
declare numorder int(11);
set numorder = (select count(count) from orderitem where orderid = old.id);
if new.isvalid ='Y' and old.isvalid !='Y' then
update goods set store = store - @numorder, salecount = salecount + @numorder where id in (select goodsid from zsorderitem where orderid = old.id);
end if;
if new.isvalid !='Y' and old.isvalid ='Y' then
update goods set store = store + @numorder, salecount = salecount - @numorder where id in (select goodsid from orderitem where orderid = old.id);
end if;
end||

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!