2

how i can convert a row inside a mysql/mariadb trigger into an json object with new JSON features?

BEGIN
    CALL my_audit_insert(tableName, id, ... JSON_OBJECT(NEW) ...);
END

Is there any possibility to get programatically columns of NEW or OLD?

First Try - Create a Statement

Idea is to get colums from system tables and get each value from NEW/OLD programatically

BEGIN
SET @s = 'SELECT NEW.? INTO @result';
PREPARE stmt FROM @s;
SET @a = 'id';

EXECUTE stmt USING @a;

CALL audit_insert(NEW.id, 'pages', JSON_ARRAY(result));
END

(1336): Dynamic SQL is not allowed in stored function or trigger

Second Idea - Select the row via PrimaryKey as JSON_Object in after-triggers

procedure spGetJson from https://stackoverflow.com/a/35957518/7080961

DROP PROCEDURE IF EXISTS `spGetJson`;
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `spGetJson`(pTableName varchar(45), pId int, out pJson JSON)
begin

select  group_concat(concat("'", COLUMN_NAME, "', ", COLUMN_NAME) separator ',')
into    @cols
from    information_schema.columns
where   TABLE_NAME = pTableName and TABLE_SCHEMA = database();

set @q = concat('select json_object(', @cols, ') INTO @a from ', pTableName);
if pId is not null then
    set @q = concat(@q, ' where id = ', pId);
end if;
set @q = concat(@q, ';');

prepare statement from @q;
execute statement;
deallocate prepare statement;

SET pJson = @a;

end//
DELIMITER;

After Insert Trigger: 

BEGIN
    CALL spGetJson('pages', NEW.id, @a);
    CALL audit_insert(NEW.id, 'pages', @a);
END

same: (1336): Dynamic SQL is not allowed in stored function or trigger

Conclusion:

  • What have you tried to achieve your wanted results? What has your research concerning your problem shown? Can you provide code of your tries? [How do I ask a good question](https://stackoverflow.com/help/how-to-ask), [How much research effort is expected](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) and [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) might be helpful to improve your question. – Geshode Jan 26 '18 at 19:37
  • This is going to be incredibly tough. I just tried to sqlfiddle this, but it doesn't appear possible (With a dynamic `JSON_OBJECT()`. You would need to `PREPARE` some SQL as a string, and pass the values to `JSON_OBJECT()` dynamically. – Blue Jan 26 '18 at 19:40
  • thank you for your idea, im sad to say mariadb/mysql does not allow dynamic sql –  Jan 26 '18 at 20:19
  • add second idea :) –  Jan 26 '18 at 23:39
  • this is a well written question and a well organized approach – Omar Apr 22 '21 at 13:30

0 Answers0