Issues with DateTime DataType

I have a MySQL 5.1 server community edition database. In a table I have a column with the name TransactionDate of DateTime DataType. Each time a new insert is done, this column value is updated with the current Date and Time values.

When I created this table, I expected a unique seconds value w开发者_如何学运维ith every insert. In some strange scenarios, MySQL is storing same Date/Time values. This is making few reports to crash. It is strange because the user has to fill a Form and it takes a couple of minutes. When the user saves a new record, current Date/Time values are stored in the TransactionDate column. There is no question of duplicate values as, at least, seconds will change.

Please suggest any better ways to handle this problem.

If you need a unique date/time, use the trigger to populate it automatically with now() when the row is created.

Bonus... Here's a working trigger definition:

DELIMITER $$
CREATE TRIGGER MYTABLE_INSERT_TRIGGER
BEFORE INSERT ON MYTABLE
FOR EACH ROW
BEGIN 
    SET NEW.TransactionDate = now();
END;$$
DELIMITER ;