mysql - 更新后调用触发器时如何从表中获取更新的行

Question

这是我的表结构

mysql> select * from product_table;
+----------------+---------------------+
| product_number | product_description |
+----------------+---------------------+
|              1 | product one         |
|              2 | product two         |
|              3 | product three       |
|              9 | product five        |
|             10 | product six         |
|             11 | product six         |
+----------------+---------------------+

我想说更新一行，比如表中的第 9 个产品编号，如下所示

UPDATE product_table SET product_description ="product seven" WHERE product_number=9;

这样在触发器中我就可以从表product_table中获取相应更新的产品编号

我像这样编写了触发器，并且它的创建没有任何错误。

DELIMITER //
CREATE TRIGGER product_table_update 
      AFTER UPDATE
       ON product_table
       FOR EACH ROW
BEGIN


       DECLARE l_product_number INT;
    set @l_table_name = 'product_table';
    set @l_action = 'updation';


              SET @l_table_column = 'product_description';
select new.product_number into @l_product_number from product_table;//  here is i think where the problem is , i am trying to fetch the updated row to l_product_number

       IF (OLD.product_description <> NEW.product_description) THEN
         SET @oldval = OLD.product_description;
         SET @newval = NEW.product_description;
         select concat(@oldval,@newval) into @l_description;

       END IF;

       INSERT INTO audit_table_test
       ( table_name,
      changed_row_id,
      action,
      table_column,
      change_desciption,
      change_time
     )
       VALUES
   ( @l_table_name,
      @l_product_number,
      @l_action,
      @l_table_column,
      @l_description,
     NOW()
     );
END; //
DELIMITER ;

然后当我尝试像这样更新

UPDATE product_table SET product_description ="product seven" WHERE product_number=11;

显示此错误

ERROR 1172 (42000): Result consisted of more than one row

我知道问题出在这段代码

select new.product_number into @l_product_number from product_table;//  here is i think where the problem is , i am trying to fetch the updated row to l_product_number

请有人帮助我获取调用此触发器的更新行

Answer 1

尝试:

...
 SET @l_table_column = 'product_description';

 /*
 here is i think where the problem is , i am trying to fetch the
 updated row to l_product_number
 */
 -- select new.product_number into @l_product_number from product_table;
 SET @l_product_number := NEW.product_number;

 IF (OLD.product_description <> NEW.product_description) THEN
...