DateCreated column in Sql Server?

Question

Is there a special way to declare a DateCreated column in a MS Sql Server table so that it will automatically fill it with the appropriate time-stamp when created?

Or.. do I have to provide the datetime to it when I do the query, manually?

Solution 1

Default values suffer from two major drawbacks.

• if the insert statement specifies a value for the column, the default isn't used.
• the column can be updated any time.

These mean that you can't be certain that the values haven't been modified outside of your control.

If you want true data integrity (so that you're sure the date in the row is the creation date), you need to use triggers.

An insert trigger to set the column to the current date and an update trigger to prevent changes to that column (or, more precisely, set it to its current value) are the way to implement a DateCreated column.

An insert and update trigger to set the column to the current date is the way to implement a DateModified column.

(edit from user Gabriel - here's my attempt to implement this as described - i'm not 100% sure it's correct but I'm hoping the OP reviews it...):

CREATE TRIGGER [dbo].[tr_Affiliate_IU] 
   ON  [dbo].[Affiliate] 
   AFTER INSERT, UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get the current date.
    DECLARE @getDate DATETIME = GETDATE()

    -- Set the initial values of date_created and date_modified.
    UPDATE
        dbo.Affiliate
    SET 
         date_created = @getDate
    FROM
        dbo.Affiliate A 
        INNER JOIN INSERTED I ON A.id = I.id
        LEFT OUTER JOIN DELETED D ON I.id = D.id
    WHERE
        D.id IS NULL

    -- Ensure the value of date_created does never changes.
    -- Update the value of date_modified to the current date.
    UPDATE
        dbo.Affiliate
    SET
         date_created = D.date_created
        ,date_modified = @getDate
    FROM 
        dbo.Affiliate A 
        INNER JOIN INSERTED I ON A.id = I.id
        INNER JOIN DELETED D ON I.id = D.id 
END

Solution 2

You can set the default value of the column to "getdate()"

Solution 3

We have DEFAULT on CreatedDate and don't enforce with Triggers

There are times when we want to set the date explicitly - e.g. if we import data from some other source.

There is a risk that Application Bug could mess with the CreateDate, or a disgruntled DBA for that matter (we don't have non-DBAs connecting direct to our DBs)

I suppose you might set Column-level permissions on CreateDate.

A half-way-house might be to have an INSERT TRIGGER create a row in a 1:1 table, so that column was outside the main table. The second table could have SELECT permissions, where the main table has UPDATE permissions, and thus not need an UPDATE trigger to prevent changes to CreateDate - which would remove some "weight" when updating rows normally.

I suppose you coul have an UPDATE/DELETE trigger on the second table to prevent change (which would never be executed in normal circumstances, so "lightweight")

Bit of a pain to have the extra table though ... could have one table for all CreateDates - TableName, PK, CreateDate. Most database architects will hate that though ...

Solution 4

Setting the default value isn't enough, you should add a trigger to prevent updating:

CREATE TRIGGER UpdateRecord ON my_table
AFTER UPDATE AS UPDATE my_table
SET [CreatedDate] = ((SELECT TOP 1 [CreatedDate] FROM Deleted d where d.[id]=[id]))

Solution 5

Certainly is.

Here is an example in action for you.

Create table #TableName
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    SomeDate VARCHAR(100)
)

INSERT INTO #TableName (SomeDate)
SELECT 'Some data one' UNION ALL SELECT 'some data two' 

SELECT * FROM #TableName 

DROP TABLE #TableName 

View more solutions

Comments

[paxdiablo]

Be aware that this can be bypassed by users since the default value isn't taken into account when you insert an actual value. And it can also be updated by users as well. It's probably good enough if you can control what SQL is run (and you can't :-) but it's not secure.

[James]

That is actually a good thing because if a user has a specific date they need to put there it probably should be allowed unless you have rules that govern otherwise.

[paxdiablo]

Respectfully disagree, @James. Have you not heard of Sarbanes-Oxley? :-) If you have a DateCreated column, it should be set to the date the row was created. If you want a user-changeable column, it should be called DateCreatedForPurposesOfFraud or something similar.

[paxdiablo]

If you want to change a trigger-controlled column, you submit a form signed by 8 levels of management, the DBAs take the database offline to users, disable the triggers, change the date, then turn everything back on. Then you have your audit trail.

[paxdiablo]

And what happens when a disgruntled employee comes along with "UPDATE #TABLE SET CREATEDDATE = GETDATE()"? Bang, there goes all your valuable information as to when the rows were created.

[John Sansom]

If you control the environment and application then you control the access to the data tier. The two concerns then become irrelevant.

[paxdiablo]

Really, you don't think someone can just connect to the DBMS with a JDBC driver? And one of the major advantages of n-tier is having each tier responsible for its own integrity. You NEVER assume the DB is protected by the application when it can protect itself. That's asking for trouble.

[paxdiablo]

"Morale", but maybe their morals should be examined as well :-) Anyway, prevention is always better than recovery. It's the difference between no downtime and some downtime (none is better than any amount). It's probably more the environment I work in, DB2 on System z, we are always paranoid.

[John Sansom]

@Pax: No I do not. An an ad-hoc connection cannot connect to an appropriately secured environment. Security is not the sole responsiblity of the DMBS (it does provide a starting point) as it should be a cooporate/platform wide strategy.

[paxdiablo]

The triggers limit attack vectors to those (hopefully few) DBAs, otherwise anyone with a DB login and write access to the table can subvert your data with the "default value" solution.

[Aaron Anodide]

I'm going to implement this right now exactly per your spec in this answer - it it all works out, should I edit this answer? (the other option is lazy for me - if you have an example of this lying around could you paste it in?)