[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Two auto-TIMESTAMP columns in one table with MySQL 5.0 - <http://www.futhark.ch/mysql/108.html>



 
    

Two auto-TIMESTAMP columns in one table with MySQL 5.0
----------------------------------------------------------------------------------------

Since MySQL 4.1 you have much finer control on the behaviour of your auto-TIMESTAMP columns: You can have them set to the actual time only on INSERTs, only on UPDATEs or on both. However you couldn't have more than one of these auto-TIMESTAMP columns in one table. With 5.0 that's now possible using TRIGGERs.

It's very common to be in need of more than one auto-TIMESTAMP column in one table, firing on different operations: The blog you're reading right now for example stores the time an article has first been published (corresponds to an INSERT operation) as well as the time an article has last been updated (corresponds to an UPDATE operation).

What you possibly want to do to achieve this is something like the following:

CREATE TABLE blog_entries (
    `published` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated` TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
    `title` VARCHAR(128)
);

But this still won't work in 5.0. The server returns an error:

ERROR 1293 (HY000): Incorrect table definition; there can be only one
TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Instead we have to take a different approach and skip the built-in functionality of TIMESTAMP columns completely. Let's define the table first:

CREATE TABLE blog_entries (
    `published` TIMESTAMP DEFAULT '0000-00-00 00:00:00',
    `updated` TIMESTAMP DEFAULT '0000-00-00 00:00:00',
    `title` VARCHAR(128)
);

We now have a table with two TIMESTAMP columns that both almost act as ordinary DATETIME columns, which means they are not set automatically. Let's add the intended functionality for the published column again using a TRIGGER that fires every time just before a new row is inserted into the table:

CREATE TRIGGER blog_entries_publish BEFORE INSERT ON `blog_entries`
FOR EACH ROW SET NEW.published = NOW(), NEW.updated = '0000-00-00 00:00:00';

In a BEFORE TRIGGER you can access and change the value to be inserted/updated via the NEW keyword. That's what we did with this TRIGGER: Whenever a new row will be added to the table blog_entries our TRIGGER fires and sets the value for the column published to the actual time at the moment of the INSERT and the column updated to its default value of a zero timestamp (meaning in our example that this row has never been updated).

Let's do almost the same for the UPDATE operation, just setting updated to the current time here and keeping the OLD value (the one being stored in the table before the UPDATE) for the column published:

CREATE TRIGGER blog_entries_update BEFORE UPDATE ON `blog_entries`
FOR EACH ROW SET NEW.updated = NOW(), NEW.published = OLD.published;

You'll notice however that this is a very strict version of auto-TIMESTAMP columns: Our TRIGGERs override whatever values we try to INSERT or UPDATE for the two columns. This means it's actually no longer possible to set these two columns manually.
     
In some situations this might be just what you want: For auditing, for example. You can now be sure that nobody ever messes around with your TIMESTAMPs (at least nobody with no rights to DROP your TRIGGERs).
     
But if you want to be less strict with your users you can still build something that more closely emulates the built-in behaviour of TIMESTAMP columns:

CREATE TABLE blog_entries (
    `published` TIMESTAMP NULL DEFAULT NULL,
    `updated` TIMESTAMP NULL DEFAULT NULL,
    `title` VARCHAR(128)
);

CREATE TRIGGER blog_entries_publish BEFORE INSERT ON `blog_entries` FOR EACH ROW SET
NEW.published = IFNULL(NEW.published, NOW()),
NEW.updated = IFNULL(NEW.updated, '0000-00-00 00:00:00');

CREATE TRIGGER blog_entries_update BEFORE UPDATE ON `blog_entries` FOR EACH ROW SET
NEW.updated = IF(NEW.updated = OLD.updated OR NEW.updated IS NULL, NOW(), NEW.updated),
NEW.published = IFNULL(NEW.published, OLD.published);

The magic functionality of our columns is now only triggered if a NULL value is written to them. By setting the two columns to DEFAULT NULL we make sure this is the case if you just leave them out in your INSERT statements.

For the UPDATE operation it's not as easy: If you don't provide a value for the updated column its NEW value will be the same as its OLD and not the default of NULL during execution of the TRIGGER. So you'll have to check for this situation as well, making it impossible to explicitly set the value of the updated column to the same as it was before with the above code. But you could of course change it and define NULL to mean "same as before" for the updated column:
     
CREATE TRIGGER blog_entries_update BEFORE UPDATE ON `blog_entries` FOR EACH ROW SET
NEW.updated = CASE
                  WHEN NEW.updated IS NULL THEN OLD.updated
                  WHEN NEW.updated = OLD.updated THEN NOW()
                  ELSE NEW.updated
              END,
NEW.published = IFNULL(NEW.published, OLD.published);
  
That's the nice thing with TRIGGERs: Just tailor it to your needs!


mysql> insert into blog_entries set title='abc';
Query OK, 1 row affected (0.05 sec)

mysql> select *  from blog_entries;
+---------------------+---------------------+-------+
| published           | updated             | title |
+---------------------+---------------------+-------+
| 2006-08-09 16:28:06 | 0000-00-00 00:00:00 | abc   |
+---------------------+---------------------+-------+
1 row in set (0.00 sec)

mysql> update blog_entries set title='def' where title='abc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *  from blog_entries;
+---------------------+---------------------+-------+
| published           | updated             | title |
+---------------------+---------------------+-------+
| 2006-08-09 16:28:06 | 2006-08-09 16:28:49 | def   |
+---------------------+---------------------+-------+
1 row in set (0.00 sec)
Google