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

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

Not sure why Bill deleted his answer as I think it is spot on: you cannot achieve this via trigger, use multiple updates wrapped into a transaction. — Shadow
– Shadow, Commented Feb 27, 2024 at 23:27

AztecCodes · Accepted Answer · 2024-02-29 07:08:10Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related