3

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?

Tomas
  • 31
  • 1

1 Answers1

3

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.

Rohit Gupta
  • 1,626
  • 6
  • 17
  • 19
  • Can you elaborate on the difference between user_id and last_user_id? – Tomas Feb 06 '23 at 16:35
  • @Tomas - Oops, I answered it the way I had it set up on my DBs in the past, assuming you had a column called user_id. I have corrected it. – Rohit Gupta Feb 06 '23 at 22:12
  • Thanks for the update. Comparing old and new for equality is a neat trick. Unfortunately, this will fail if the same user updates the same row multiple times with same last_user_id, which erroneously stores NULL. – Tomas Feb 07 '23 at 01:17
  • Yes it will fail, I should have added that caveat. – Rohit Gupta Feb 07 '23 at 11:31