We recently upgraded from SQL Server to MySQL 5.0 and things have been great except for the way MySQL handels default timestamp fields. In our DB, we have many tables with 2 fields: ModifiedDate and CreatedDate. In the conversion process these fields came across as DateTime fields, but I have no opposition to changing them to TimeStamp fields if I can get the behavior I want.

In SQL Server both fields were defined with default = getdate() so when a new record was inserted the current date and time was put into the fields (I don't care if they're a couple nano-seconds off). When I updated a record, I had to make the code update the ModifiedDate. I can't seem to get this behavior w/ MySQL (we have a ton of code I don't want to have to go and change). Seems MySQL wants to update timestamp fields with the current time every time the record is updated (not ideal for CreatedDate). I just want the defaults to be entered when the record is created and I'll take it from there. Any idea how I can do this?

One other issue is that the DB I want to do this on is already in production, so it would be extremely painful to have to create new tables. I would like to just update the existing tables if at all possible.

Thanks,
Mark