mysql trigger after query with join

Question

I want to create a trigger that set new org_id when status changes to 'stock'.
I have 2 tables: functionalci that has attribute org_id and physicaldevice that has status. The update that invokes the trigger is like:

UPDATE functionalci f
  JOIN physicaldevice d
    ON f.id = d.id
   SET d.status = 'stock', f.name ="XXX"  
 WHERE d.id = 2

When I try a trigger like:

CREATE TRIGGER trig1
AFTER UPDATE ON physicaldevice 
FOR EACH ROW  
BEGIN 
    UPDATE functionalci SET org_id=2  WHERE functionalci.id = OLD.id;
END;

I have this error: mysql_errno = 1442, mysql_error = Can't update table 'functionalci' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Is there any way to do the trigger without changing the UPDATE that invokes it?

Answer 1

Finally I will create some mysql events to solve the problem. Something like:

CREATE EVENT ev 
ON SCHEDULE EVERY 1 MINUTE
DO
    UPDATE functionalci f
    JOIN physicaldevice d
    ON f.id = d.id
    SET f.org_id = 2
    WHERE f.org_id <> 2 AND d.status LIKE "stock"   

It's not the same as a trigger, but it works for me.