Solved

New functionality needed - populate new record with date added information

Posted on 2016-07-19
10
26 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
  • 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
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

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 26

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 26

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now