mysql - 错误 1442 : Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Question

我有一个我想要的表:当表被更新时，该表的 2 个字段(标题和描述)发生变化并从另一个表中获取值

这是我的触发器:

drop trigger trigger_trade_request ;
CREATE TRIGGER trigger_trade_request AFTER UPDATE ON `trade_request` 
FOR EACH ROW 
BEGIN 

  IF NEW.title = null THEN

    UPDATE `trade_request_type`,`trade_request` 
       SET NEW.title = `trade_request_type`.title ,
           NEW.description = `trade_request_type`.description
     WHERE `trade_request_type`.id = NEW.trade_request_typeId;

  END IF;   
END;

我的表:

• 贸易请求类型
• 交易请求

错误:

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

没用!概率是多少？

Answer 1

1. 使用 is null 检查空值。
2. 要更改更新行的值，请使用 BEFORE 触发器和 NEW 行上的 SET。

试试这个:

CREATE TRIGGER trigger_update_request BEFORE UPDATE ON `trade_request`
FOR EACH ROW
BEGIN
    IF NEW.title IS NULL THEN
        SET NEW.title = (SELECT title FROM trade_request_type
            WHERE trade_request_type.id = NEW.trade_request_typeId);
        SET NEW.description = (SELECT description FROM trade_request_type
            WHERE trade_request_type.id = NEW.trade_request_typeId);
    END IF;
END;

也许您可以使用 SELECT...INTO 语法代替 SET，如下所示:

CREATE TRIGGER trigger_update_request BEFORE UPDATE ON `trade_request`
FOR EACH ROW
BEGIN
    IF NEW.title IS NULL THEN
        SELECT title, description
        FROM trade_request_type
        WHERE trade_request_type.id = NEW.trade_request_typeId
        INTO NEW.tile, NEW.description;
    END IF;
END;