MySQL Forums
Forum List  »  Triggers

Dynamic column name while operating with NEW/OLD
Posted by: Alexander Panyutin
Date: February 04, 2015 07:52AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Dynamic column name while operating with NEW/OLD
6097
February 04, 2015 07:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.