CODEWITHSUNDEEP
mysqlsqltriggersmysql-error-1442
afsane
afsane

Reputation: 1919

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?

Upvotes: 2

Views: 15637

Answers (2)

Xint0
Xint0

Reputation: 5389

  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;

Upvotes: 5

Petar Ivanov
Petar Ivanov

Reputation: 93030

This is because you are trying to update the other table too. Try that:

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

Upvotes: 0

Related Questions