Find which columns have changed in MySQL triggers

Question

I am working on an audit requirement for a legacy application. The legacy application was written in Java using JDBC with an underlying DB in MySQL. I need to populate an audit table with any changes for a given table, the audit table DDL is something like this

CREATE TABLE ENTITY_AUD (
    id INT AUTO_INCREMENT PRIMARY KEY,
    baseTableId INT FK,
    beforeValue INT,
    afterValue INT,
    changedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    changedBy VARCHAR2
);

I have to do this via triggers, since we want to avoid any changes to the legacy Java code. I know when can can know the old and new values of a given column by using the old and new variables, something like this

DELIMITER $$

CREATE TRIGGER ENTITY_AFTER_UPDATE
AFTER UPDATE
ON Entity FOR EACH ROW
BEGIN
    IF OLD.quantity <> new.quantity THEN
        INSERT INTO ENTITY_AUD(baseTableId,beforeValue, afterValue,changedBy)
        VALUES(old.id, old.quantity, new.quantity,new.updated_by);
    END IF;
END$$

DELIMITER ;

How can I dynamically find all the columns which have changed and populate new rows for each column updates? I do not want if statements for each column, the source tables may have 40 columns.

score 1 · Accepted Answer · answered Jan 31 '22 at 17:43

It would be best to use a CDC tool like Debezium for this instead of triggers.

You can get a list of columns belonging to your Entity table by querying INFORMATION_SCHEMA.COLUMNS, but that doesn't help. It's not possible to generate dynamic statements in a trigger based on the columns, because MySQL doesn't support running dynamic SQL in a trigger.

If you must use triggers, then you just have to hard-code all 40 columns.

You could streamline it a little bit, by using the new feature in MySQL 8.0 feature to add a WHERE clause to a SELECT without a table reference. If the WHERE clause is false, the SELECT returns zero rows. Then you can string these together with UNION to make a set of rows, one for each column that changed.

BEGIN
    INSERT INTO ENTITY_AUD (baseTableId, beforeValue, afterValue, changedBy)
    SELECT OLD.id, OLD.quantity, NEW.quantity, NEW.updated_by
    WHERE OLD.quantity <> NEW.quantity
    UNION 
    SELECT OLD.id, OLD.price, NEW.price, NEW.updated_by
    WHERE OLD.price <> NEW.price
    UNION
    SELECT OLD.id, OLD.billing, NEW.billing, NEW.updated_by
    WHERE OLD.billing <> NEW.billing
    UNION
    ...37 others...
END

This question has come up numerous times in the past. Here are a few I found, but undoubtedly there are more.

Thanks @bill-karwin, for the response. yes i dit see the links you shared for not able to dynamically find changed columns. The where clause i was npt aware of will try that — Tathagata Roy, Jan 31 '22 at 20:27

Find which columns have changed in MySQL triggers

1 Answers1