Get current updated column name to use in a trigger

Posted by Serge on Stack Overflow See other posts from Stack Overflow or by Serge
Published on 2010-06-09T18:21:01Z Indexed on 2010/06/09 19:32 UTC
Read the original article Hit count: 247

Filed under:
|
|
|

Is there a way to actually get the column name that was updated in order to use it in a trigger?

Basically I'm trying to have an audit trail whenever a user inserts or updates a table (in this case it has to do with a Contact table)

CREATE TRIGGER `after_update_contact`
    AFTER UPDATE ON `contact` FOR EACH ROW
    BEGIN
        INSERT INTO user_audit (id_user, even_date, table_name, record_id, field_name, old_value, new_value)
        VALUES (NEW.updatedby, NEW.lastUpdate, 'contact', NEW.id_contact, [...])
    END

How can I get the name of the column that's been updated and from that get the OLD and NEW values of that column. If multiple columns have been updated in a row or even multiple rows would it be possible to have an audit for each update?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about database