Solved

New functionality needed - populate new record with date added information

Posted on 2016-07-19
10
32 Views
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.
0
Comment
Question by:jamesmetcalf74
[X]
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
10 Comments
 
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.
0
 

Author Comment

by:jamesmetcalf74
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
0
 
LVL 20

Accepted Solution

by:
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).
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Closing Comment

by:jamesmetcalf74
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?
0
 
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.
0
 
LVL 27

Expert Comment

by:Zberteoc
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!
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41721173
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41721180
@Russ,

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.
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41721286
@Zberteoc

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

@jamesmetcalf74

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.
0
 
LVL 27

Expert Comment

by:Zberteoc
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.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running total between 2 sql tables in Sql 6 50
semaphore timeout period has expired 1 29
get count of orders by customer Sql Server table. 3 46
SQL Query 9 26
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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