November 3, 2008 at 9:43 am
DB Platform: SQL Server 2005 Enterprise Ed (64)
The Situation: Existing table used to store logins for a thid party Application has no column for tracking time stamp data when a new login is created. We need to modify this so we can perform mertics on new logins based on Date & Time created. We have added a new column to the table of type DATETIME to track this data, by setting the columns value equal te current date & time when a new record is inserted.
The Question: What is the best/recomended method to configure this column to store the curreent date & time so that no future records added to the table will contain a Null for this column but so that existing records in this table can have a Null value in this column. We want to prevent any future records from having a Null for this date/Time column but we also need to leave existing records as is whcih means Null for this column.
Comments?
NOTE: The SQL Platform is 2005 however the databaase compatability level is set to 8.0 (SQL 2000) and we can not change that.
Thanks
Kindest Regards,
Just say No to Facebook!November 3, 2008 at 10:22 am
Hi,
If I understood your dilemma correctly - you can add a check constraint with the WITH NOCHECK option.
HTH!
/Elisabet
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 3, 2008 at 10:27 am
Set the default value for this new colum to equal getdate() , this will ensure that any new data added will have a date and this wont affect the older data
November 3, 2008 at 10:49 am
Have to agree with steveb, use a default of getdate() fro the column.
November 3, 2008 at 11:25 am
Thanks to everyone frr the replys but unfortunately using a Default isn't working.
The column is a new custom colum so the third party application should not be aware of it but somehow the column default is not working. Every recorded added to this table over the weekend has a Null value for this column. If it was simply a column that was part of the orginal table and not being used by the third party app, then one could assume that the application is explictly specifying a NULL for the column when inserting data but this is a new custom column.
Any idea how an application when it perfroms inserts could over ride a column's default especially when it a new column that the application should not be aware of ?
Thanks
Kindest Regards,
Just say No to Facebook!November 3, 2008 at 11:57 am
I can't answer your question, but I did a test (code follows), and when I added the DateAdded column with a default, it was populated.
create table #Test(
TestID int,
TestVal1 varchar(10),
TestVal2 varchar(10),
TestVal3 varchar(10)
);
insert into #Test (TestID, TestVal1, TestVal2, TestVal3)
select 1, 'Joe','Sam','Fred';
insert into #Test (TestID, TestVal1, TestVal2, TestVal3)
select 2, 'Abby','Hannah','Heather';
insert into #Test (TestID, TestVal1, TestVal2, TestVal3)
select 3, 'Snowball','Sassy','TJ';
insert into #Test (TestID, TestVal1, TestVal2, TestVal3)
select 4, 'K1','K2','K3';
select * from #Test;
go
alter table #Test
add DateAdded datetime
CONSTRAINT DateAdded_default
DEFAULT getdate() ;
select * from #Test
insert into #Test (TestID, TestVal1, TestVal2, TestVal3)
select 5, 'Joe','Sam','Fred';
insert into #Test (TestID, TestVal1, TestVal2, TestVal3)
select 6, 'Abby','Hannah','Heather';
insert into #Test (TestID, TestVal1, TestVal2, TestVal3)
select 7, 'Snowball','Sassy','TJ';
insert into #Test (TestID, TestVal1, TestVal2, TestVal3)
select 8, 'K1','K2','K3';
select * from #Test
drop table #Test;
Try out this code in a development sandbox and see if it works for you.
November 3, 2008 at 12:08 pm
You may find it's not so unaware of the column. If it's trying to passa null value in, it will allow it through.
You could always look at using an INSTEAD OF trigger to make sure that the default value is put in.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 3, 2008 at 12:23 pm
Hi,
If you want a default value; use a Default getdate(), it you want to enforce a NOT NULL constraint; add a CHECK constraint. Or both.
You can add the CHECK constraint if you use ALTER TABLE... WITH NOCHECK even if you have NULL values in that column.
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 3, 2008 at 12:34 pm
Elisabeth Rédei (11/3/2008)
Hi,If you want a default value; use a Default getdate(), it you want to enforce a NOT NULL constraint; add a CHECK constraint. Or both.
You can add the CHECK constraint if you use ALTER TABLE... WITH NOCHECK even if you have NULL values in that column.
HTH!
/Elisabeth
Per the original post, existing records will have a null value for this field, so a not null constraint won't work.
November 3, 2008 at 12:43 pm
After some testing I've verified that the column dtCreated is set to NULL only when it is explicitly referenced in th INSERT statement. Since this column is new & custom to our database/Table I can only assume the piece of code that connecting (from outside our LAN) and populating this table is first checking the tables structure and altering the INSERT to include any additonal columns.
Can anyone else think of any way something like this could happen short of the application checking for table structure changes and adding to the INSERT statement, code/values for any additional custom columns?
Thanks
Kindest Regards,
Just say No to Facebook!November 3, 2008 at 12:44 pm
Just giving the column a DEFAULT will not help you. Your third party application is not aware of the column, so it is not going to include the DEFAULT keyword in the insert statement.
You have a couple of options:
1) Change the column to not allow NULL and put in a value to represent the old NULL values (such as 1/1/1900).
2) Use a trigger to populate the new column on inserted records.
You can use a constraint to make the value necessary, but without making the column not allow NULL, the only way to get it populated without making the application aware of the column is a trigger.
I would absolutely recommend using option 1 and coming up with something to fill in the old NULL values.
November 3, 2008 at 12:50 pm
If you check the code I posted, you'll see that I created a table, inserted some records, then added a column, and inserted additional records. I used the exact same code for both sets of inserts (no modifications to account for the added column), and it worked appropriately for me in my test environment. The second set of records did get a default date added during the insert process.
November 3, 2008 at 1:02 pm
Especially because it is a 3th party product, I do not advise to add columns to tables.
To achieve the thing you want, create another logging table and populate that using an insert trigger on the original table.
pro:
- you can apply your own naming convention to your log-object
- no problems if the 3-th party uses select * from
- no problems if the 3-th party uses ado rowsets to create new rows.
- you control your simple insert trigger
- only point in time you have to check if the trigger still exists, is at (schema) upgrade time.
contra:
- only point in time you have to check if the trigger still exists, is at (schema) upgrade time.
Remark: make sure you can differentiate your triggers form the ones supplied by the 3-th party (naming conventions)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 4, 2008 at 10:58 am
Michael Earl (11/3/2008)
Just giving the column a DEFAULT will not help you. Your third party application is not aware of the column, so it is not going to include the DEFAULT keyword in the insert statement.You have a couple of options:
1) Change the column to not allow NULL and put in a value to represent the old NULL values (such as 1/1/1900).
2) Use a trigger to populate the new column on inserted records.
You can use a constraint to make the value necessary, but without making the column not allow NULL, the only way to get it populated without making the application aware of the column is a trigger.
I would absolutely recommend using option 1 and coming up with something to fill in the old NULL values.
Actually you don't have to include DEFAULT in an insert for the column to be populated with the Default value upon an INSERT. I have verified this with this table which is how I was able to determine that the 3rd Party application is doing something prior to the INSERT that makes it aware of this new column and is explictly specifying a NULL value in the INSERT for it.
This company is NULL crazy using NULL for any and every column in any table when a record in the table is INSERTED and no explict value is provided at the time of the INSERT. We even have some tables that store transactional info whcih have NULL values for columns that contain currency values. How does one justify a NULL value for a currency field when it is a transaction (i.e. Charge, Receipt, Payment, ect)? I'd be suprised if between all of the companies develoeprs they have even 1 day of some type of formal SQL training. I'm certain most (not all) have at least read trhu some of the classic SQL for Dummies book but beyond that, SQL training for them is a priority below low.
Thanks for the reply though!
Kindest Regards,
Just say No to Facebook!November 4, 2008 at 10:58 am
ALZDBA (11/3/2008)
Especially because it is a 3th party product, I do not advise to add columns to tables.To achieve the thing you want, create another logging table and populate that using an insert trigger on the original table.
pro:
- you can apply your own naming convention to your log-object
- no problems if the 3-th party uses select * from
- no problems if the 3-th party uses ado rowsets to create new rows.
- you control your simple insert trigger
- only point in time you have to check if the trigger still exists, is at (schema) upgrade time.
contra:
- only point in time you have to check if the trigger still exists, is at (schema) upgrade time.
Remark: make sure you can differentiate your triggers form the ones supplied by the 3-th party (naming conventions)
I had some reservations about changing the table till I found out it's use is restricted to an add-on to our primary application that will probably be used by just a few clients. One of the developers has already confirmed via email that aside from storing user login info (users external to the company, aka clients) and using that info to verify users when they connect, they do not interact with the table in any other way and have no plans to. Half of the existing columns in the table are not even going to be used.
That said your comments are well put; best practice is to use a separte table populated via TRIGGER. After this recent discovery that the thing is explcitly NULL'ing my custom column, I think we'll be creating the custom table. I just hate to do this just to track a single piece of data when it is something the existing table not only should be doing but has a column setup to track this info but doesn't because the developers for whatever reason didn't want to complete that part of the INSERT. Oh well!
Thanks for the reply!
Kindest Regards,
Just say No to Facebook!Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply