MySql and SQL settings before update trigger Updates of different tables/columns cannot update tables in stored functions/triggers

Question

I have a join table with 2 tables (A and C) with n:m relationship. The join table (B) also holds some information.

A ----- B ------ C

In table A, I have a when_modified column that I always want to keep up to date if anything changes. This should include changes on Table B.

So I have a pre-update trigger on table B with the following query

UPDATE A SET when_modified = NOW() WHERE id = NEW.id;

The problem is that if I now execute an update query (join on B) I get the following error

Cannot update table 'A' in a stored function/trigger because it is already used by a statement that calls this stored function/trigger.

Update B vv JOIN A v on vv.id = v.id SET vv.block = 1 WHERE v.status = 'finished'

Answer 1

You need to rewrite the UPDATE statement.

You cannot update in a trigger or this might happen. Infinite loop

But simple changes will produce the same effect

CREATE TABLE A (id int, status  varchar(10), when_modified  datetime)

INSERT INTO A VALUES (1,'finished',now())

CREATE TABLE B (id int, block int)

INSERT INTO B VALUES(1,0)

Update B vv JOIN A  v on vv.id = v.id SET vv.block = 1, v.when_modified = NOW()  WHERE v.status = 'finished'

SELECT * FROM A

id	state	when_modified
1	Finish	2023-05-15 20:31:15

SELECT * FROM B

id	piece
1	1

violin