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

Question

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:

• trade_request_type
• trade_request

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?

Answer 1

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;