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?
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.
paxdiablo
2009-02-17 05:22:13
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.
James
2009-02-17 05:22:48
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
2009-02-17 05:30:01
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
2009-02-17 05:32:02
+7
A:
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.
paxdiablo
2009-02-17 05:10:36
If you control the environment and application then you control the access to the data tier. The two concerns then become irrelevant.
John Sansom
2009-02-17 10:02:52
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
2009-02-17 10:32:39
@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.
John Sansom
2009-02-17 10:43:07
I never suggested it was the sole responsibility, but it *is* the primary responsibility. Each tier should defend itself from attack. If you other tiers also defend the DB, that's fine, but the DB shouldn't rely on it.
paxdiablo
2009-02-17 13:53:49
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.
paxdiablo
2009-02-17 13:55:37
A:
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
John Sansom
2009-02-17 08:52:40
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.
paxdiablo
2009-02-17 09:36:12
@Pax: Would not happen on my turf however, in the unlikely event it did, I would simply implement our DR procedures and recover the data.Sounds like you need to look at your security strategy and staff moral :-)
John Sansom
2009-02-17 10:00:07
"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.
paxdiablo
2009-02-17 10:35:55
+1
A:
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 ...
Kristen
2009-02-17 11:31:26