"created" and "updated" columns in mysql table drupal

Mysql does not allow two auto-updating timestamps columns in the same database table. However, many people like to have "created" and "updated" columns for their tables because that sort of information is useful, so there must be some work around. From what I have gathered, one column must be a datetime and the other must be a timestamp. The datetime column can function as the created column and when defaulted to null, upon insert with a trigger it will automatically take the current timestamp value. The timestamp column can function as the updated column and can be configured to be auto-updated as the mysql documents describe. These two pages are useful for understanding this:

Mysql Datetime and Timestamp Types

Auto Initialization and Updated for Timestamp

Where it gets tricky is implementing this with drupal's schema function. Has anyone found a successful solution to this in drupal 7?

After toying with the syntax and different suggestions from many other stackoverflow questions/answers, I finally found the right combination of small code additions to have automatic "created" and "updated" columns in my database table.

In my hook_schema() function:

$schema['table'] = array(
  'fields' => array(
    'created' => array(
      'mysql_type' => 'datetime',
      'not null ' => TRUE,
      'default' => format_date(time(), 'custom', 'Y-m-d 00:00:00'),
    ),
    'updated' => array(
      'mysql_type' => 'timestamp',
      'not null' => TRUE,
    ),
  ),
  //other parts of the schema (indexes, keys...)
);

In my hook_install() function:

//Automatically set created datetime for current timestamp upon insert
$query = db_query('
  CREATE TRIGGER triggername BEFORE INSERT ON tablename FOR EACH ROW BEGIN SET
    NEW.created=NOW(); END
');

//Automatically update 'updated' to current timestamp whenever a row is changed
$query = db_query('
  ALTER TABLE tablename
  MODIFY updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
');

The 'triggername' is whatever you want to name your trigger. The 'tablename' is the name of the table you established in hook_schema() that has the created and updated columns.

Hopefully this can be of use to somebody. When I was looking into this a couple weeks ago, there were no posts with someone doing this within drupal and it was very difficult to figure out how to adapt the different suggestions to the drupal framework. This is the best solution I figured out. If you have another one you believe to be better go ahead and post it!