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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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).
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.


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 27

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 27

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 27

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

627 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