SQL - Creating a Table with Two Timestamp Column with No Default Value

Question

I want to create a table with two column, one "created" and one "modified", with a timestamp type. I don't want them to have a default value. But when I create them, it automatically puts CURRENT_TIMESTAMP as default for at least one of them, and also on update.

How can I just have type timestamp with no default or on update value for any of my column?

Edit: I use MySQL

What's wrong with CREATE TABLE (TIMESTAMP val1, TIMESTAMP val2 ...);? — Waleed Khan
– Waleed Khan, Commented Jul 15, 2012 at 1:27

Kevin Aenmey · Accepted Answer · 2012-07-15 02:06:44Z

1

If you are using SQL Server, the timestamp type is used for row versioning, not for storing an actual date or time value. See MSDN.

You could create the columns with a datetime datatype and then set your created and modified values with triggers.

CREATE TRIGGER trg_SetDateCreated ON MyTable
FOR INSERT AS 
    UPDATE MyTable 
    SET created = CURRENT_TIMESTAMP
    WHERE MyTable.id = (SELECT Id FROM Inserted);
GO

CREATE TRIGGER trg_SetDateModified ON MyTable
FOR UPDATE AS 
    UPDATE MyTable
    SET modified = CURRENT_TIMESTAMP
    WHERE MyTable.id = (SELECT Id FROM Inserted);
GO

edited Jul 15, 2012 at 2:06

answered Jul 15, 2012 at 1:43

Kevin Aenmey

13.4k5 gold badges48 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cam Over a year ago

I use My SQL. I didn't know that I should use datetime for SQL, nor the trigger solution. Thanks!

cam Over a year ago

I tried thatCREATE TRIGGER trg_SetDateCreated ON users FOR INSERT AS UPDATE users SET us_created = CURRENT_TIMESTAMP WHERE users.us_id = ( SELECT us_id FROM Inserted ) ; and I get a syntax error...

Kevin Aenmey Over a year ago

Ok, my solution was for SQL Server, not MySQL. Perhaps someone else can provide the syntax for creating the equivalent triggers in MySQL. It looks like this site would be a good place to get you started: dev.mysql.com/doc/refman/5.0/en/create-trigger.html

baash05 Over a year ago

The only problem is.. if you have the exact same data going in as the original record, you'll still have the time stamp changed.

Collectives™ on Stack Overflow

SQL - Creating a Table with Two Timestamp Column with No Default Value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related