mysqlsqltriggersmysql-error-1442

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


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

This is my trigger:

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;

My tables:

error:

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?


Solution

    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.

    Try this:

    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;
    

    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;