有个产品表proTable(id,sum,num,.....)
id:产品id流水号sum:产品的总数num:产品目前剩余数现在想要实现当修改此表时 num随着sum的修改而更新 即:修改后的num=(修改后的sum-修改前的sum)+原来的num我写的是
update proTable set new.num=(new.sum-old.sum)+old.num;可是修改表时就报错,请问和这个触发器该什么写啊
id:产品id流水号sum:产品的总数num:产品目前剩余数现在想要实现当修改此表时 num随着sum的修改而更新 即:修改后的num=(修改后的sum-修改前的sum)+原来的num我写的是
update proTable set new.num=(new.sum-old.sum)+old.num;可是修改表时就报错,请问和这个触发器该什么写啊
set new.num=(new.sum-old.sum)+old.num;
Can't update table 'proTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
update proTable set new.num=(new.sum-old.sum)+old.num;改成set new.num=(new.sum-old.sum)+old.num;就应该可以了。
你的意思是直接写在sql语句里 不能用trigger了是吧,mysql的trigger对本表不支持是吧
update proTable set new.num=(new.sum-old.sum)+old.num;改成set new.num=(new.sum-old.sum)+old.num;
不好意思 没看明白,现在可以了。可是订单的触发器不好使了,加上了 vipper23 和ACMAIN_CHM 说的后 订单的不生效了
订单表order(id proId num)
id:流水号proId:单品idnum:订购数量
订单的触发器如下:
1.after updateupdate proTable set num=num-new.num where id=new.proId2.after deleteupdate proTable set num=num+old.num where id=old.proId这2个 不好用了 我晕
为什么写了这个之后,订单的trigger就不生效了啊,为什么、?
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `v1_xl`.`proTable` BEFORE UPDATE
ON `v1_xl`.`proTable`
FOR EACH ROW BEGIN
SET new.num=(new.sum-old.sum)+old.num;
END$$
DELIMITER ;
order上的trigger:
after insert:DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `v1_xl`.`order` AFTER INSERT
ON `v1_xl`.`order`
FOR EACH ROW BEGIN
UPDATE proTable SET num=num-new.num WHERE id=new.proId;
END$$DELIMITER ;2.after delete:DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `v1_xl`.`order` AFTER DELETE
ON `v1_xl`.`order`
FOR EACH ROW BEGIN
UPDATE proTable SET num=num+old.num WHERE id=old.proId;
END$$
DELIMITER ;