不断更新?mysql触发器

tgabmvqs  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(249)

即使我把它插入了一个包含零值的变量的if语句中(如果qty/sku\u stock已经相等),mysql仍然将它视为一个无休止的循环,给我一个错误:
无法更新存储函数/触发器中的表,因为它已被调用此存储函数/触发器的语句使用
//下面的代码

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 ;
2w3rbyxf

2w3rbyxf1#

第二个触发器似乎是多余的,除非您要做的是在两个表中的任何一个更新时同步它们。例如,在这种情况下,您可能需要多表更新而不是触发器。

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)

相关问题