获取当前更新的列名以在触发器中使用

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)

基本上我只是在用户插入或更新表时尝试进行审计跟踪(在这种情况下,它与Contact表有关)

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?

如何获取已更新的列的名称,并从中获取该列的OLD和NEW值。如果连续更新了多列甚至多行,是否可以对每次更新进行审核?

2 个解决方案

#1

Just use OLD.colname <> NEW.colname for each column to check and find those that are different. Triggers are a bit limited in their use in MySQL, too bad.

只需对每列使用OLD.colname <> NEW.colname来检查和查找不同的列。触发器在MySQL中的使用有点受限,太糟糕了。

#2

Try this code...

试试这个代码......

create table sales(orderno INT, sale INT,empsalary int, ts TIMESTAMP);

create table history(updated varchar(20), oldvalue INT,newvalue INT);

INSERT INTO sales (orderno,sale,empsalary) VALUES(1,700,7000);

INSERT INTO sales (orderno,sale,empsalary) VALUES(2,800,8000);

INSERT INTO sales (orderno,sale,empsalary) VALUES(3,900,9000);

DROP TRIGGER test.instrigger;



DELIMITER ///

CREATE TRIGGER test.instrigger AFTER UPDATE ON sales
FOR EACH ROW

BEGIN

    IF NEW.sale <> OLD.sale THEN  
        INSERT INTO history (updated, oldvalue, newvalue) VALUES('sale', OLD.sale,NEW.sale);

    END IF;
    IF NEW.empsalary <> OLD.empsalary THEN  
        INSERT INTO history (updated, oldvalue, newvalue) VALUES('empsalary', OLD.empsalary,NEW.empsalary);
    END IF;
END;
///

DELIMITER ;

#1