获取当前更新的列名以在触发器中使用 [英] Get current updated column name to use in a trigger

问题描述

是否有一种方法可以真正获取已更新的列名，以便在触发器中使用它?

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

基本上，无论何时用户插入或更新表(在这种情况下，它都与Contact表有关)，我都试图进行审计跟踪

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

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

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.colname<> NEW.colname即可检查并查找不同的列.触发器在MySQL中的使用受到一些限制，太糟糕了.

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.

这篇关于获取当前更新的列名以在触发器中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！