How to check which fields modified in after update trigger in MySQL

Question

I have created an after-update trigger on a table that has only 4 columns. So, I applied the below conditions in my trigger -

IF (NEW.courseStartDate <> OLD.courseStartDate )
    THEN
        INSERT INTO tp_courses_audit (`row_id`, `orgId`, `field_name`, `old_value`
        , `new_value`, `created` )
            VALUES (OLD.id, OLD.orgId, 'courseStartDate',OLD.courseStartDate,NEW.courseStartDate,NOW());
    END IF;
    
    IF (NEW.courseEndDate <> OLD.courseEndDate)
    THEN
        INSERT INTO tp_courses_audit (`row_id`, `orgId`, `field_name`, `old_value`
        , `new_value`, `created` )
            VALUES (OLD.id, OLD.orgId, 'courseEndDate',OLD.courseEndDate,NEW.courseEndDate,NOW());
    END IF;
    
    IF (NEW.course_id <> OLD.course_id)
    THEN
        INSERT INTO tp_courses_audit (`row_id`, `orgId`, `field_name`, `old_value`
        , `new_value`, `created` )
            VALUES (OLD.id, OLD.orgId, 'course_id',OLD.course_id,NEW.course_id,NOW());
    END IF;
    
    IF (NEW.status <> OLD.status)
    THEN
        INSERT INTO tp_courses_audit (`row_id`, `orgId`, `field_name`, `old_value`
        , `new_value`, `created` )
            VALUES (OLD.id, OLD.orgId, 'status',OLD.status,NEW.status,NOW());
    END IF; 

The trigger working fine, and I am getting the data in my audit table.

I just want to know, there should definitely be a better way to identify which field/fields were modified in the last update, because if we have a large number of columns in my main table then multiple if conditions will not be a better way to handle this.

How can I handle it in a better way?

Answer 1

The trigger working fine

No. If old or new value is NULL then the change won't be logged.

You must use NULL-safe equal to operator:

If NOT (OLD.column <=> NEW.column) THEN

Additionally you'd define created column as DEFAULT CURRENT_TIMESTAMP and remove it from INSERT.