mysql - 在orders_id与更新过程相同的orders表上触发mysql

Question

我想更新表订单上的状态 = 1 当表上的订单状态 = 1 时，订单 ID = 订单 ID 与更新相同。

CREATE TRIGGER upd_check AFTER UPDATE ON orders_foods
FOR EACH ROW
BEGIN
   IF (new.status= '1' where old.order_id=new.order_id) THEN
     update  orders 
     set status= '1'
     where old.order_id=old.order_id
   END IF;
END;

订单表 orders_foods 表

我不知道如何在更新过程中写入该 order_id 值以触发并使其与订单表上的 id 匹配

我的问题解决了

BEGIN
    declare od_status_1 int default 0;
    declare od_number int default 0;
    select count(*) into od_number from orders_foods where orders_foods.order_id = new.order_id;
    select count(*) into od_status_1 from orders_foods where orders_foods.order_id = new.order_id and orders_foods.status = 1;

 if od_number = od_status_1 then
   update  orders
  set status= '1'
  where id=old.order_id;
 end if;

结尾

感谢 P.Salmon

标签： mysql

Answer 1

尝试这个

Delimiter //  
CREATE TRIGGER upd_check AFTER UPDATE ON orders_foods
    FOR EACH ROW
    BEGIN
       IF (new.status= 1 and old.order_id=new.order_id) THEN
         update  orders 
         set status= 1
         where id=old.order_id;
       END IF;
    END;
//
Delimiter ;