drop trigger if exists WW; DELIMITER// CREATE TRIGGER WW AFTER INSERT ON td_orders FOR EACH ROW BEGIN DECLARE p_price DECIMAL(10, 2); DECLARE o_zk INT; DECLARE p_no INT; DECLARE p_stock INT; SELECT Gdiscounts INTO o_zk FROM tb_grade WHERE Gno in (select Gno from tb_card where Ino = NEW.Ino); SELECT Pprice,Pcount,pno INTO p_price,p_stock ,p_no FROM tb_products WHERE pno = NEW.pno; SELECT Pcount INTO p_stock FROM tb_products WHERE pno = NEW.pno; -- FOR UPDATE; IF p_stock < NEW.Ocount THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足,无法完成操作'; ELSE UPDATE tb_products SET Pcount = p_stock - NEW.Ocount WHERE pno = p_no; UPDATE td_orders SET Omoney = p_price * o_zk WHERE Ino = NEW.Ino; END IF; END// DELIMITER; 这段代码是什么意思?运行后报错‘1442 - Can't update table 'td_orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.’
时间: 2024-02-25 13:57:47 浏览: 78
C++编程中__if_exists与__if_not_exists语句的用法
这段代码是一个MySQL的触发器,用于在`td_orders`表中插入新数据时,根据订单中商品的价格和优惠信息自动计算订单金额,并更新商品的库存数量。具体实现过程如下:
1. 首先从`tb_grade`表中查询当前用户对应的等级优惠信息,将结果存储在变量`o_zk`中。
2. 然后从`tb_products`表中查询商品的价格、库存数量和商品编号,将结果存储在变量`p_price`、`p_stock`和`p_no`中。
3. 接着判断库存数量是否足够,如果不足则抛出一个异常,否则更新`tb_products`表中对应商品的库存数量,并根据价格和优惠信息计算订单金额,更新`td_orders`表中对应订单的`Omoney`字段。
4. 最后,该触发器执行完毕。
但是,运行后报错 `1442 - Can't update table 'td_orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.`,这是因为该触发器中更新了`td_orders`表,而MySQL不允许在触发器中更新触发该触发器的表。所以,需要修改代码使其不更新`td_orders`表,或者将更新操作放在触发器外部进行。
具体修改方法可以参考以下代码:
```
DROP TRIGGER IF EXISTS WW;
DELIMITER //
CREATE TRIGGER WW AFTER INSERT ON td_orders FOR EACH ROW
BEGIN
DECLARE p_price DECIMAL(10, 2);
DECLARE o_zk INT;
DECLARE p_no INT;
DECLARE p_stock INT;
SELECT Gdiscounts INTO o_zk FROM tb_grade WHERE Gno IN (SELECT Gno FROM tb_card WHERE Ino = NEW.Ino);
SELECT Pprice, Pcount, pno INTO p_price, p_stock, p_no FROM tb_products WHERE pno = NEW.pno;
IF p_stock < NEW.Ocount THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足,无法完成操作';
ELSE
-- 更新商品库存数量
UPDATE tb_products SET Pcount = p_stock - NEW.Ocount WHERE pno = p_no;
-- 在外部更新订单金额
UPDATE td_orders SET Omoney = p_price * o_zk WHERE Ino = NEW.Ino AND Oid = NEW.Oid;
END IF;
END //
DELIMITER ;
```
以上代码中,我们将更新订单金额的操作放在了触发器外部进行,通过在更新语句中加入`AND Oid = NEW.Oid`条件,确保只更新对应订单的金额。这样可以避免在触发器中更新`td_orders`表而导致的错误。
阅读全文