New functionality needed - populate new record with date added information

Posted on 2016-07-19
Last Modified: 2016-07-20
I have a table that is now date sensitive, but was not before.
most of my tables have a column that identifies when the record was added or inserted into the table.

My problem is this.
this table is generated by an application that i have no access to as far as how it populates a table.

is there a way for me to add a column to a table that auto senses when the add takes place and populates a "dateadded" column automatically.
i also have to make sure that this new column doesn't effect the application in anyway.

obviously this solution would not be retroactive and i am ok with that.
Question by:jamesmetcalf74
  • 5
  • 3
  • 2
LVL 20

Expert Comment

by:Russ Suter
ID: 41719948
Yes, just add a new DateTime column to your existing table and set its "Default Value or Binding" property to GETDATE(). To maintain backward compatibility with existing rows you'll need to allow nulls (at least at first).
Here's a screenshot of how you'd do it in SSMS.
ScreenshotIf you would later like to index on that column you'll need to remove null values first. That can easily be done by backfilling your table with an old date, say, 1/1/1753 (oldest DateTime value allowed in SQL Server) using a simple Update statement where your new column IS NULL.

Author Comment

ID: 41719971
that worked on my local test database when i imported it through the import wizard.
Are you pretty confident that this won't effect the way the application is writing to this table?
Should i add this column after hours and restart sql or anything.

i am pleased with my test scenario
LVL 20

Accepted Solution

Russ Suter earned 500 total points
ID: 41719976
Adding a column to an existing table should rarely cause a problem with existing code. The only time it might is if you use a SELECT * FROM... somewhere, especially on an INSERT OR UPDATE. That's why it's generally considered poor practice to SELECT * from anything.

Adding it with a default value of GETDATE() means that any existing INSERT into that table won't break since SQL will automatically insert the default value if none is provided.

There's no need to restart SQL. The only reason why you might want to wait until after hours is if the table you're working on has a lot of records. With a sufficiently tall table it can take a couple of minutes to add the column during which time users may experience poor SQL performance. However, whether you add it in the next 5 minutes or wait until everyone goes home any errors that may pop up will happen starting immediately after the update.

Short answer, if your table doesn't have too many rows then go ahead and add it now. It's just as easy to remove the column again if you encounter any issues (which I doubt you will).
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Closing Comment

ID: 41719997
worked like a charm...
follow up question.
what about adding a modified date?
when a record has just one value updated or changed?
LVL 20

Expert Comment

by:Russ Suter
ID: 41720000
That's not quite as simple. The Default Value or Binding only applies to new rows that are created. To create similar functionality for an UPDATE statement you would need to implement a trigger. I'm not a fan of triggers. They're difficult to debug and I've yet to see any database implementation where triggers are properly documented.

What I would generally do is add the necessary DateTime column with the Allow Null option then modify my code so it alters the column value when an update is performed. The only downside to this (and upside to the trigger approach) is that the column wouldn't be updated if the table was directly edited.
LVL 26

Expert Comment

ID: 41721166
I was ready to  suggest that instead of adding a column, which it is possible to affect applications if there are places where no explicit columns list is used, could be even inside the database, like views, functions or procedures, not only inside the application. I agree adding a date with default is the easiest and recommended way but if it was not implemented from start could create problems.

Triggers are perfectly fine and they don't cause any issues if they are correctly implemented. You could create a history table for your original table and insert in it data, with dates, any time is inserted or updated. This way your code will not be affected in any way, not in the database and not in the app and you will have a real complete info of what happened to your table, when and even who did it. So with one trigger you shoot both rabbits!
LVL 20

Expert Comment

by:Russ Suter
ID: 41721173
LVL 26

Expert Comment

ID: 41721180

There e is  no need to give me links about triggers. I have been working with databases for over 20 years now, of which last 8 as a DBA and I used extensively triggers in every company I worked for. This issue is more like an urban myth and not a real scare. We NEVER had issues with triggers, they are there for a reason and they are really useful. SQL system processes are using triggers in the background especially for this kind of reasons like audit but also for replications, practically there is no other way to do theese things.

Point is that there is no reason to avoid triggers but you definitely have to use them properly like any other SQL feature for that matter.
LVL 20

Expert Comment

by:Russ Suter
ID: 41721286

I wasn't giving YOU anything. I was simply providing more information to the author.


Ultimately the choice is yours. The trigger / no trigger debate is alive and well and there are arguments for and against both. I've had some very bad experiences with triggers in the past. A properly implemented (and documented) trigger can be helpful but I have yet to come across such a thing. My advice is to avoid them if at all possible. There are usually equally effective and far less problematic ways to solve problems.

Regardless, your initial question was answered well within the constraints you provided and without the use of a trigger. Glad I could help.
LVL 26

Expert Comment

ID: 41721308
There is no debate. Triggers are useful and NECESSARY. I am NOT talking necessarily about THIS question, that can be partly solved by adding a created_date column with getdate() default(which is the right way), but is not guaranteed it will not brake something down the road as the asker didn't have the chance to test all the application and database code where that table is used. In regards to updated_date column you CANNOT solve the problem without trigger! Period.

My advice is that if you decide to use trigger go for it but make sure you don't do the same mistakes are highlighted in that article, because the truth is there is no problem with the trigger only with the people who don't understand them well. There is a comment to the article, from Maurice Pelchat, that argues about this fact very clearly and it should also be read, not just the article.

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query question 12 32
Database ERD 4 30
SQL Availablity Groups List 2 8
MS SQL SERVER and ADODB.commands 8 23
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question