Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

New functionality needed - populate new record with date added information

Posted on 2016-07-19
10
Medium Priority
?
35 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 2000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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