错误1442:无法更新存储函数/触发器中的表，因为调用该存储函数/触发器的语句已使用该表 [英] ERROR 1442: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

问题描述

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

I have a table that I want : when the table has been updated, 2 fields of that (title and description) change and take value from another table

这是我的触发器:

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;

我的桌子:

• trade_request_type
• trade_request

错误:

错误1442:无法更新存储函数/触发器中的表'trade_request'，因为调用该存储函数/触发器的语句已使用该表.

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.

它不起作用！什么是概率?

It does not work! What is the prob?

推荐答案

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

1. Use is null to check for null values.
2. To change the value of the updated row use BEFORE trigger and SET on the NEW row.

尝试一下:

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，如下所示:

Probably you can use the SELECT...INTO syntax instead of SET, like so:

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;

这篇关于错误1442:无法更新存储函数/触发器中的表，因为调用该存储函数/触发器的语句已使用该表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！