TIMESTAMP Columns and Timestamp Function Calls Using MySQL Triggers

by

It is a common practice to add fields such as `created’ or `last_updated’ to a database table to keep track of newly created objects or of updated ones from a date and time perspective.

MySQL has very useful date/time functions such as `NOW(),’ and `CURRENT_TIMESTAMP()’ or `CURRENT_TIMESTAMP’ which actually are both synonyms for `NOW(),’ as clearly explained in the MySQL manual available on the MySQL website. It is possible to use such useful functions to automatically populate database fields that hold datetime information from within MySQL itself as an alternative to set those fields from an application.

The MySQL version that was used when writing this post was `5.1.50.’ It does not seem to be possible to apply function calls like CURRENT_TIMESTAMP() on more than one TIMESTAMP field within the same table. For instance, if we execute the following SQL statement:

  -- Example: *wrong* `CREATE TABLE' query
  DROP TABLE IF EXISTS `test`.`tbl_testing`;
  CREATE TABLE `test`.`tbl_testing` (
    `test_id` INT NOT NULL AUTO_INCREMENT ,
    `test_title` VARCHAR(128) NOT NULL ,
    `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ,
    `last_updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP() ,
    PRIMARY KEY (`test_id`)
  );

 

we will get the following error from MySQL:

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

 

If we create the two TIMESTAMP fields such as they are both NULL and do not contain any CURRENT_TIMESTAMP() calls:

  [...]
    `created` TIMESTAMP ,
    `last_updated` TIMESTAMP ,
  [...]

 

and we pass null values for both of them on an INSERT statement, it seems to be widely known that MySQL will automatically populate both fields with the current timestamp, even if we did not specify any value for each field on the CREATE TABLE query. It should also be widely known that, for instance, if we write a query and update for the `test_id’ field only without referencing the timestamp fields in the SQL statement, MySQL will update the timestamp value on the first TIMESTAMP column only. In this case it is possible to invert the order of the two fields so `last_updated’ comes first and gets updated.

But what if we need to refactor our table(s) and we forget about this detail (field order), or if we pass a null value by mistake to the `created’ column by referencing it on an UPDATE statement? In both cases the `created’ field will be always updated with the current timestamp, and in the first case the `last_updated’ field will not hold the right data. In other words, it could be a tad difficult to do some troubleshooting — and it could also be a bit painful to maintain our code when we have many tables. We also want to make sure that the timestamp records in our rows are correct.

A good trade-off in terms of code maintenance and data consistency could be possible by using triggers without worrying about the order of the fields and what we pass as parameters for them in our queries. For instance, we might want to store the creation date for a newly created row and leave the `last_updated’ field blank in order to quickly determine if a row has been updated since its creation. But we can obviously set the same datetime value to both fields if we prefer. On an UPDATE statement, the `created’ field will remain unchanged, and the `last_updated’ column will be holding the last datetime information. The way we will create the trigger will also provide for two aspects: even if we specify either null or not null values for the `created’ and `last_updated’ fields, both those fields will always hold the original creation date and the last updated datetime info:

  -- we will be using triggers for `created' and `last_updated'
  DROP TABLE IF EXISTS `test`.`tbl_testing`;
  CREATE TABLE `test`.`tbl_testing` (
    `test_id` INT NOT NULL AUTO_INCREMENT ,
    `test_title` VARCHAR(128) NOT NULL ,
    `created` TIMESTAMP NULL ,
    `last_updated` TIMESTAMP NULL ,
    PRIMARY KEY (`test_id`)
  );

  USE `test`;

  DELIMITER $$
  -- (re)-creating the "before insert" trigger
  DROP TRIGGER IF EXISTS `tr_testing_bi` $$
  CREATE TRIGGER `tr_testing_bi` BEFORE INSERT ON `tbl_testing`
    FOR EACH ROW BEGIN
      SET NEW.`created` = CURRENT_TIMESTAMP();
      SET NEW.`last_updated` = NULL;
    END;
  $$
  -- (re)-creating the "before update" trigger
  DROP TRIGGER IF EXISTS `tr_testing_bu` $$
  CREATE TRIGGER `tr_testing_bu` BEFORE UPDATE ON `tbl_testing`
    FOR EACH ROW BEGIN
      SET NEW.`last_updated` = CURRENT_TIMESTAMP();
      SET NEW.`created` = OLD.`created`;
    END;
  $$
  DELIMITER ;

 

We can observe the following:

  • we are still using the TIMESTAMP data type for both the `created’ and `last_updated’ fields and their position in the fields list does not matter;
  • both the `created’ and `last_updated’ fields do not have any CURRENT_TIMESTAMP() function calls in the CREATE TABLE statement and are set to NULL;
  • the `tr_testing_bi’ trigger will set the `created’ field (NEW.`created` data) with a valid timestamp and whatever value we specify on the INSERT query for the `created’ field will be overwritten with the information returned by the CURRENT_TIMESTAMP() MySQL function;
  • the `tr_testing_bi’ trigger will also be responsible for setting the `last_updated’ field to `NULL’, as we are just adding a new row;
  • the `tr_testing_bu’ trigger will make sure the same logic applies to the `last_updated’ field — the last updated data that will be set to the `last_updated’ field (NEW.`last_updated` data) will be coming from the output of the CURRENT_TIMESTAMP() function as well;
  • in the `tr_testing_bu’ trigger code, we also make sure the original value for the `created’ field is preserved — in the event of an erroneous update for that field, we will always set this value to its original one: `SET NEW.created = OLD.created’ will perform this check for us;
  • we are using the CURRENT_TIMESTAMP() function on both triggers to create timestamps, but, of course, we can also use its aliases, NOW() or CURRENT_TIMESTAMP, or the UTC_TIMESTAMP() function (or UTC_TIMESTAMP) if we wish to store our timestamp data using the UTC format in our database.

Although we will have a bit more SQL code to jot down because we added two triggers — each for a different event (BEFORE INSERT and BEFORE UPDATE), we should also have a more robust structure and a strict approach that helps prevent manual updates for the timestamp fields.

One Response to “TIMESTAMP Columns and Timestamp Function Calls Using MySQL Triggers”

  • al nik says:

    Since all my tables contain ”created” and “last_updated” columns I was wondering if it is possible to setup one common trigger like the one you suggest for all my tables.. and if yes how

Leave a Reply