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

Question

Schema and data for test database - https://gist.github.com/koceg/435c0d2b1246a69d048f

My goal is to update boards table, when somebody inserts a new row in the objects_properties table. The name of the column to update is dynamic - it depends on a property_id from objects_properties.

So far I've created a trigger and a stored procedure, but I'm getting this error:

Dynamic sql is not allowed in stored function or trigger.

Am I doing something wrong or is mysql not allowing to call 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 a pseudocode. Real SQL will be even worse, because there will be a few dozens 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;

P.S. I can't move this logic to my application, because this database is used by several applications.

Answer 1

The Current MySQL (5.7) Manual section D.1 Restrictions on Stored Programs states that

1. SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).
2. Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. For a list of statements supported as prepared statements .
3. Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program. Prepared statement scope is the current session, not the stored program, so the statement could be executed after the program ends, at which point the variables would no longer be in scope. So you can see that it's not permitted.

Regards.