Create a Mysql trigger on Update, to update another row in the same table

Question

i have a table named tblcustomfieldsvalues where i want to create a trigger for updating a row in the same table, this is the table : Table screenshot

The goal is to create conditions like this :

• if the corresponding value = 'some car' where relid=relid and fieldid=11, the updated row will bi id=1
• if the corresponding value = 'another car' where relid=relid and fieldid=11, the updated row will bi id=4 and so on...

I even tried to create a temporary table but i always have the error like ( infinite loop )...

Answer 1

I tried another approach, created another table as log_table and populate it by insert after updating the first table:

BEGIN
  IF NEW.fieldid = 30 THEN
    INSERT INTO log_km (km, voiture)
    SELECT NEW.value, value FROM 
customfieldsvalues WHERE relid = NEW.relid AND fieldid = 11;
  END IF;
END

It worked to populate the second table.

Then I created another trigger on the second table to update the first one, I had the error.

How can I do it? There is the trigger for the second table on insert:

BEGIN
UPDATE customfieldsvalues 
SET value = NEW.voiture
WHERE id = 22;
END