Add auto setting created and modified timestamps to a database table.

gistfile1.txt

-- The table
CREATE TABLE `my_table` (
  field1 varchar(50),

  ....

  createdTime datetime NOT NULL, -- NOT NULL is important to trigger the trigger
  modifiedTime TIMESTAMP -- Timestamps automatically update on insert
);

-- Create a trigger to set time on create
DELIMITER //
DROP TRIGGER IF EXISTS `my_table_before_insert`//
CREATE TRIGGER `my_table_before_insert`
BEFORE INSERT ON `my_table`
FOR EACH ROW
BEGIN
    -- Only set the default value if it's empty
    IF NEW.createdTime = '0000-00-00 00:00:00' THEN
        SET NEW.createdTime = NOW();
    END IF;
END;//
DELIMITER ;


-- 
-- What this does:
--   1) Takes advantage of mysql timestamps to automatically update a row when something is updated
--   2) Create a trigger to set a create timestamp when you first insert the record, if and only if you haven't already provided 
--      a value