Muninn: Better time stamping on TABLE contacts

[codeswitcher]

Since I'm making TABLE contacts editable, I'd better have an updated_on TIMESTAMP field....

Except MySQL, for reasons unknown, doesn't permit more than one TIMESTAMP field on a table. So this is a job for trigger functions.

So I added a column, updated_on of type TIMPSTAMP that is nullable and defaults to null. Then...

CREATE TRIGGER `contacts_updated_on_ts_update` BEFORE UPDATE ON `contacts`
 FOR EACH ROW BEGIN
	IF NEW.updated_on IS NULL THEN
        SET NEW.updated_on = CURRENT_TIMESTAMP;
	END IF;
END

And while we're at it, we'd better handle INSERTs, too:

CREATE TRIGGER `contacts_updated_on_ts_insert` BEFORE INSERT ON `contacts`
 FOR EACH ROW BEGIN
	IF NEW.updated_on IS NULL THEN
        SET NEW.updated_on = CURRENT_TIMESTAMP;
	END IF;
END

Let's make it as if that had always been there:

UPDATE `contacts` SET `updated_on`=`created_on` WHERE `updated_on` IS NULL

Beautiful.