Bug #116731 | Allow creating JSON_OBJECT in trigger from NEW/OLD | ||
---|---|---|---|
Submitted: | 20 Nov 2024 14:15 | Modified: | 20 Nov 2024 15:58 |
Reporter: | Oleg Andreyev | Email Updates: | |
Status: | Unsupported | Impact on me: | |
Category: | MySQL Server | Severity: | S4 (Feature request) |
Version: | OS: | Any | |
Assigned to: | CPU Architecture: | Any |
[20 Nov 2024 14:15]
Oleg Andreyev
[20 Nov 2024 14:29]
MySQL Verification Team
HI Mr. Andreyev, Thank you for your feature request. However, when you ask for a new feature, you should define it entirely in MySQL syntax and not use non-standard syntax of other products. You should read our Reference Manual that you can find on https://dev.mysql.com and very precisely define your feature request based ONLY on the syntax that MySQL supports. We can not verify a feature request that is not based on the SQL Standard. So, please use SQL Standard 2016 and what it supports and try to define VERY precisely your feature request, based on MySQL features only. Unsupported.
[20 Nov 2024 15:45]
Oleg Andreyev
Updated my example to match MySQL Syntax as much as possible. ```sql create trigger changelog_trigger after update on account for each row BEGIN -- it would be great to create JSON from NEW or OLD variables in trigger, without "hardcoding" all columns set @old_data = JSON_OBJECT(OLD.*); set @new_data = JSON_OBJECT(NEW.*); insert into changelog (body) values (json_object('action', action, 'table_name', table_name, 'transaction_id', transaction_id, 'timestamp', timestamp, 'old_data', @old_data, 'new_data', @new_data)); END; ```
[20 Nov 2024 15:48]
MySQL Verification Team
Hi Mr. Andreyev, Can you try using JSON_FUNCTION() to achieve the same result ??? Please, check it out ........
[20 Nov 2024 15:58]
Oleg Andreyev
To which function JSON_* you are referring to? https://dev.mysql.com/doc/refman/8.4/en/json-function-reference.html Probably I can use information_schema to find all columns than concat everything to single string for JSON_OBJECT, and use prepare/execute inside trigger to get compiled result... but looks not friendly for developers.
[20 Nov 2024 16:04]
MySQL Verification Team
Sorry, I misspelled it ........... It is this one: https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#function_json-value or any other on this page: https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html One important note ........ if your feature is not described in SQL Standard 2016, it is unlikely that it would be implemented.