mysql - 如何解决错误: ERROR 1442 (HY000) Can't update table 'table name' in stored function/trigger?

Question

我有一个 mysql 程序，它将记录从一个主数据库更新到其他数据库:以下是我的程序

DELIMITER $$
CREATE PROCEDURE order_update (vmasterdb varchar(200),vdbnamelist VARCHAR(8000),tbl_name varchar(200),form_id varchar(200))
BEGIN
    DECLARE dbnamelist VARCHAR(8000);
    DECLARE pos int;
    DECLARE dbname VARCHAR(100);

    SET dbnamelist=vdbnamelist;
 WHILE LENGTH(dbnamelist) > 0 DO

        SET pos  = INSTR(dbnamelist,',') ;
        SET dbname = (CASE WHEN pos>0 THEN SUBSTRING(dbnamelist, 1, pos-1) else SUBSTRING(dbnamelist, 1) END);

IF tbl_name='tabOrder Form' THEN
SET @myCommand := concat('
    UPDATE ',vmasterdb,'.`tabOrder Form` b
           JOIN ',dbname,'.`tabOrder Form` a
           ON a.name = b.name
    SET
a.docstatus = b.docstatus,
a.parent = b.parent,
a.parentfield = b.parentfield,
a.order_calculation = b.order_calculation,
a.custom_customer = b.custom_customer
WHERE a.name=?');
    PREPARE myStatement FROM @myCommand;
    SET @c1 =form_id;
    EXECUTE myStatement USING @c1;
    DEALLOCATE PREPARE myStatement;

 END IF;
        if pos=0 then
    SET dbnamelist =null;
    else
        SET dbnamelist = SUBSTRING(dbnamelist, pos+1, LENGTH(dbnamelist)-pos);
    SET pos=0;
        end if;
    END WHILE;
 END$$
DELIMITER ;

当我使用以下函数调用此过程时

call generic_update_ord ('9fdcb2f441fcdd2e','e6d1e768ce7674e8,d5d776576c6042b6','tabOrder Form','ORD-00012')

我收到错误

ERROR 1442 (HY000): Can't update table 'tabOrder Form' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

在我的函数调用中，我的主数据库是(9fdcb2f441fcdd2e)，其他两个数据库是(e6d1e768ce7674e8)和(d5d776576c6042b6)，我希望更新其他数据库一次我的主数据库正在更新。

Answer 1

我明白了，我已经添加了一个触发器，但忘记将其删除，这就是我收到此错误的原因。