mysql - 无限循环更新？ MySQL触发器

Question

即使我将它插入到一个包含零值的 var 的 if 语句中(如果 qty/sku_stock 已经相等)，mysql 仍然将其视为无限循环，给我一个错误:

Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

//下面的代码

DELIMITER &&
create trigger after_update_db2_t1 
after update on sample_db_two.product_inventory_tb for each row 
begin 

set @qty_col_ctr = 0;

if(new.qty != old.qty)
then
    set @qty_col_ctr := 1;
end if;

if(@qty_col_ctr = 1)
then

    update sample_db_one.products_sizes_tb set sku_stock = new.qty where sku_code=@sku_no;

end if;

END&&
DELIMITER ;

//--------------------------------------------------------------------------------------------//

DELIMITER &&
create trigger after_update_db1_t1 
after update on sample_db_one.products_sizes_tb for each row 
begin 

set @qty_col_ctr = 0;

if(new.sku_stock != old.sku_stock)
then
    set @qty_col_ctr := 1;
end if;


if(@qty_col_ctr = 1)
then

    update sample_db_two.product_inventory_tb set qty = new.sku_stock

end if;


END&&
DELIMITER ;

Answer 1

第二个触发器似乎是多余的，除非您想要做的是在其中一个表有更新时同步这两个表。在这种情况下，您可能需要多表更新而不是触发器。

DROP TABLE IF EXISTS T1,t2;

create table t1(sku int,stock int);
create table t2(sku int,qty int);

insert into t1 values(1,10),(2,10);
insert into t2 values(1,10),(2,10);

update t2 join t1 on t1.sku = t2.sku
set t2.qty = 5,
     t1.stock = case when t2.qty <> 5 then 5 end
where t2.sku = 1;

update t1 join t2 on t1.sku = t2.sku
set t1.stock = 5,
     t2.qty = case when t1.stock <> 5 then 5 end
where t1.sku = 2;

select * from t1 join t2 on t2.sku = t1.sku;    

+------+-------+------+------+
| sku  | stock | sku  | qty  |
+------+-------+------+------+
|    1 |     5 |    1 |    5 |
|    2 |     5 |    2 |    5 |
+------+-------+------+------+
2 rows in set (0.00 sec)