本人新学MySQL,写了一个触发器。
两个表,一个是产品表,里面有产品的单价,第二个表是订单表,输入订单信息(订单号,产品,数量)之后,用触发器去完成金额的填写。
步骤是Insert 订单表后,根据产品信息去产品表中查询单价,然后单价×数量,获得总金额,更新订单表中的金额字段,但是插入数据之后老报错,Error Code: 1442. Can't update table 'order_items' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
代码如下,请老司机指点迷津:DELIMITER $$
create trigger update_order_items_amount after insert ON order_items
for each row begin
DECLARE curPrice double(14,2);
DECLARE curAmount double(14,2);
SELECT PRICE INTO curPrice FROM PRODUCTS
WHERE MFR_ID=NEW.MFR_ID AND PRODUCT_ID=NEW.PRODUCT_ID;
set curAmount=NEW.QTY*curPrice;
UPDATE ORDER_ITEMS SET AMOUNT=curAmount WHERE ITEM_ID=NEW.ITEM_ID;
end $$
DELIMITER ;
两个表,一个是产品表,里面有产品的单价,第二个表是订单表,输入订单信息(订单号,产品,数量)之后,用触发器去完成金额的填写。
步骤是Insert 订单表后,根据产品信息去产品表中查询单价,然后单价×数量,获得总金额,更新订单表中的金额字段,但是插入数据之后老报错,Error Code: 1442. Can't update table 'order_items' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
代码如下,请老司机指点迷津:DELIMITER $$
create trigger update_order_items_amount after insert ON order_items
for each row begin
DECLARE curPrice double(14,2);
DECLARE curAmount double(14,2);
SELECT PRICE INTO curPrice FROM PRODUCTS
WHERE MFR_ID=NEW.MFR_ID AND PRODUCT_ID=NEW.PRODUCT_ID;
set curAmount=NEW.QTY*curPrice;
UPDATE ORDER_ITEMS SET AMOUNT=curAmount WHERE ITEM_ID=NEW.ITEM_ID;
end $$
DELIMITER ;
create trigger update_order_items_amount after insert ON order_items
for each row begin
DECLARE curPrice double(14,2);
DECLARE curAmount double(14,2);
SELECT PRICE INTO curPrice FROM PRODUCTS
WHERE MFR_ID=NEW.MFR_ID AND PRODUCT_ID=NEW.PRODUCT_ID;
set curAmount=NEW.QTY*curPrice;
set NEW.AMOUNT=curAmount;
end $$
DELIMITER ;
创建的时候就出现错误:Error Code: 1362. Updating of NEW row is not allowed in after trigger
创建的时候就出现错误:Error Code: 1362. Updating of NEW row is not allowed in after trigger换成 after
英语可以直接到翻译工具中翻译一下。