Mysql Question posted in
The official MySQL documentation can be found here.

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

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 )…

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.

    2: 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

  2. 0

    You can’t use a trigger in MySQL to do what you describe.

    https://dev.mysql.com/doc/refman/8.0/en/faqs-triggers.html#faq-mysql-can-triggers-tables

    A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

    The reason for this is that MySQL Server can’t guarantee that the trigger won’t cause an infinite loop. That is, an UPDATE spawns a trigger trigger, the trigger updates the same table, which spawns another execution of a trigger, which executes another update, and so on.

    The only way it can be sure to prevent this is to disallow INSERT/UPDATE/DELETE against the same table that spawned the trigger.

    So you can’t use a trigger to do what you want.

    The way to solve this problem is that you have to write multiple updates from your client application.

    Use a transaction so all the updates are committed together.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search