using ON UPDATE for non-timestamp columns

Question

Timestamp columns may have DEFAULT NOW() ON UPDATE NOW() which is awesome to keep track of when the row was updated.

I want to have similar functionality with user id, such that when an update statement is executed with last_user_id = 42 then the value 42 is stored. But when the update statement is missing the assignment of last_user_id it is automatically reset to NULL, to indicate that the last modification to the row has happened in a context without any user.

Is it possible to use DEFAULT ON UPDATE for non-timestamp columns? If not, is it possible to achieve this behavior with triggers?

Answer 1

Yes, triggers are the perfect candidate. Something like this.

CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
IF old.last_user_id = new.last_user_id THEN
   set new.last_user_id = null;
END IF;

The old. and new. values are available for updates. So it checks if last_user_id has changed, and if not, sets it to null.

The only problem is that if the same user updates it, last_user_id will get set to null. The way to get around this is to have another sacrificial field say by_user (tinyint) that is set to 0 or 1 to indicate how it's being updated. And you never have to store its value, which will mean no extra storage

CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
IF new.by_user = 0 THEN
   set new.last_user_id = null;
END IF;
set new.by_user = null;

To clarify, the new field is always being stored as null, so has minimal impact on database size.