Hi, all.
I've trying to make next:
1. I have 5 ( or more ) tables, changes in which I need to monitor externally.
2. So I've trying to create triggers model like that:
CREATE TRIGGER `thisTriggerName` AFTER INSERT ON `myTableName`
FOR EACH ROW BEGIN
SET @table_name := 'myTableName';
SET @current_version := '';
SET @columns := '';
SELECT `version`, `columns` INTO @current_version, @columns FROM `lookup_tables_versions` WHERE `table` = @table_name;
SET @current_version := @current_version + 1;
SET @data :='';
SET @pos := 0;
SET @operative_columns := CONCAT_WS('', @columns);
split: WHILE ( LENGTH( @operative_columns ) > 0 ) DO
SET @pos := LOCATE( ',', @operative_columns );
IF ( @pos = 0 ) THEN SET @pos := LENGTH( @operative_columns ) + 1;
END IF;
SET @field := SUBSTRING( @operative_columns, 1, @pos - 1 );
SET @operative_columns := SUBSTRING( @operative_columns, @pos + 1 );
SET @data := CONCAT_WS( '', @result, '<', @field, '>', NEW.@field, '</', @field, '>' );
END WHILE split;
INSERT INTO `lookup_tables_logs` SET `table`=@table_name, `event`='insert', `pk`=NEW.id, `version`=@current_version, `data`=@data
REPLACE INTO `lookup_tables_versions` SET `table`=@table_name, `version`=@current_version, `columns`=@columns;
END;
1. Is there any way to get NEW.@field ( @field is a variable, which value means one of columns set )
2. Maybe exists some serialization function ? ( I mean `data` = SERIALIZE( NEW ) )
3. Is there any way to proceed with binlog in SQL-like syntax ?
Thank you for your time.