How to update a table from a trigger when the column name is dynamic?

Question

Scheme and data for the test database https://gist.github.com/koceg/435c0d2b1246a69d048f

My goal is to update the board table when someone inserts a new row into the objects_properties table. The column name for the update is dynamic — it depends on the id property of objects_properties .

So far I have created a trigger and a stored procedure, but I get this error:

Dynamic sql is not allowed in a stored function or trigger.

Am I doing something wrong or does mysql not allow calling a stored procedure with a prepared statement inside a trigger? If so, how can I do what I want?

I have an idea, but it's ugly even in pseudo-code. Real SQL will be even worse because there will be dozens of codes:

SWITCH (property_code) CASE 'name' INSERT INTO boards (id, name) VALUES (@object_id, @value) ON DUPLICATE KEY UPDATE name = @value; CASE 'address' INSERT INTO boards (id, address) VALUES (@object_id, @value) ON DUPLICATE KEY UPDATE address = @value; CASE 'district' INSERT INTO boards (id, district) VALUES (@object_id, @value) ON DUPLICATE KEY UPDATE district = @value; 

PS I can’t move this logic into my application because this database is used by several applications.

Answer 1

Current MySQL Section (5.7) Manual D.1 Restrictions on stored programs state that

• Ready-made SQL statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not in stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you create statements as strings and then execute them).
• As a rule, statements that are not allowed in prepared SQL reports are also not allowed in saved programs. List of statements supported as prepared instructions.
• Since local variables are in scope only during saved program execution, references to them are not allowed in prepared statements created in a saved program. The prepared report area is the current session, not the saved program, so the statement can be executed after the program ends, after which the variables will no longer be in scope. So you can see that this is not allowed.

Sincerely.