MySQL Triggers get column name
I'm inserting every update or insertion into a lo开发者_运维问答g table. And I'm using triggers to perform this.
My question is, is there a way to know which column was updated or inserted information and how to retrieve that column name **not the value ?
For example:
Contact Information changed the phone of the user, so I want to put in Logs the name of the column, not the value changed.
User | Name | Phone
There is no (simple/built-in) way to get the column name's that were updated. You will have to write a check for each column. You could muck around with prepare sql and the information_schema table, but writing checks for each column will be cleaner and faster.
FOR EACH ROW BEGIN
SET @Cols = 'Updated Columns: ';
IF OLD.col1 <> NEW.col1 THEN
@Cols = CONCAT(@Cols, 'col1, ');
END IF;
IF OLD.col2 <> NEW.col2 THEN
@Cols = CONCAT(@Cols, 'col2, ');
END IF;
.....
END
精彩评论