MySql AFTER UPDATE trigger with field names

Question

I have two tables listed below. When a field is updated on "switches" I need to insert an entry to "switch_updates" that includes the name of the field updated, the old value, and the new value.

I've included the trigger I've started but am having trouble figuring out how to pull the name of the field that was updated.

---------------------
|  switches         |
---------------------
|  id               |
|  name (varchar)   |
|  functional (int) |
---------------------


-----------------------------
|  switch_updates           |
-----------------------------
|  id                       |
|  field_name (varchar)     |
|  field_original (varchar) |
|  field_new (varchar)      |
-----------------------------

Trigger

CREATE TRIGGER SwitchUpdate_Trigger
AFTER UPDATE ON switches
FOR EACH ROW
BEGIN
    INSERT into switch_updates (id, switch_id, field_name, field_original, field_new)
    VALUES 
        (
            null,
            NEW.id, -- switch_id
                  , -- field_name?
            OLD.field_value, --?
            NEW.field_value, --?
        );
END;

Answer 1

You can't directly find the fieldname that is updated.

What you can get is the value the row (fields) had, and the value the row (fields) will have (OLD and NEW).
You could compare the old to the new value for all fields (manually), and if it is changed, you should insert that to your table.

Possible problems:

• You can't find out if a field wasn't in the query, or was 'updated' with the same value. In both cases the OLD en NEW value will be the same
• It's kinda labourfull

But in the end you could get what you want like this?

Answer 2

From the create trigger documentation:

You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

From an interesting blog:

In an INSERT trigger, only NEW.col_name can be used as there is no old row. In a DELETE trigger, only OLD.col_name can be used as there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before they were updated and NEW.col_name to refer to the columns of the row after they were updated.

This means that you should be able to complete your task.