触发mysql未知表 [英] trigger mysql unknown table

问题描述

我一直在尝试解决这个问题.这是代码:

I've been trying to solve this problem. Here is the code:

CREATE TRIGGER some_trigger AFTER UPDATE ON table_a
    FOR EACH ROW BEGIN
    DECLARE tname VARCHAR(20);
     IF (table_a.field_offer=1) THEN
      SET tname='table_b';
    ELSEIF (table_a.field_offer=0) THEN
      SET tname='table_c'; 
    END IF;   
      IF ((new.field_state = 0)) THEN
       UPDATE tname join table_a on tname.ID=table_a.ref_field
       SET tname.STOCK = tname.STOCK -1;
      ELSEIF ((new.field_state = 1)) THEN
       UPDATE tname join table_a on tname.ID=table_a.ref_field
       SET tname.STOCK = tname.STOCK +1;
      END IF;  
    END;

我得到了:

字段列表中的未知表'table_a'.field_offer和field_state可以为空.

Unknown table 'table_a' in field list. Field_offer and field_state can be null.

推荐答案

我在下面显示了对该问题的评论中所说的内容:

I've shown below what was said in the comments to the question:

CREATE TRIGGER some_trigger AFTER UPDATE ON table_a
    FOR EACH ROW BEGIN
    DECLARE tname VARCHAR(20);
    IF (NEW.field_offer=1) THEN
       UPDATE `table_b` 
       SET STOCK = CASE NEW.field_state 
                   WHEN 0 THEN STOCK - 1 
                   WHEN 1 THEN STOCK + 1 
                   ELSE STOCK 
                   END
       WHERE ID=NEW.ref_field
       ;
    ELSEIF (NEW.field_offer=0) THEN
       UPDATE `table_c` 
       SET STOCK = CASE NEW.field_state 
                   WHEN 0 THEN STOCK - 1 
                   WHEN 1 THEN STOCK + 1 
                   ELSE STOCK 
                   END
       WHERE ID=NEW.ref_field
       ;
    END IF;
    ...

请注意，由于MySQL不允许您更改触发表的数据，因此我从 UPDATE ... JOIN 更改了更新.当您实际上并没有更新table_a时，JOIN足以使MySQL反对...而联接将更新 table_b/c 中与匹配的每一行> table_a ，而不仅仅是与触发器中的值或行中的值相关的 table_a 行.

Note that I changed the updates from UPDATE ... JOIN as MySQL does not allow you to change the data of the triggered table; while you were not actually updating table_a, the JOIN could have been enough for MySQL to have objected... that and the joins would've updated every row in table_b/c that had a match in table_a, not just table_a rows related to the values in or row of the trigger.

这篇关于触发mysql未知表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！