How to stopp Null insertions
-
SQL Server 2000
I work in support and so I have no way to alter what development does. There is 1 table which contains a couple of Numeric columns that are set to Allow Nulls whcih under no circumstance shoudl ever have a null value. The insertion of Nulls in one or more of these columns has caused numerous hours of support time for our clients. I thought I had found a way to deal with this by setting each column with a Default value. I misunderstood exactly how Default works; the code inserting the data has to request the default value. Since I can not change any of the code I'm stuck tryinging to find a way to deal with Null insertions. Unfortunately simply removing the ability to Allow Nulls is not an option because when we do that several key functions (enough of them at least) cease to work.
Does anyone know of a way to replace a Null with a 0 aside from using a Trigger? I'm fearful of settinig up Triggers on this table due to it's volatility.
Thank You
Kindest Regards,
Just say No to Facebook! -
1) go bash those developers and teach them how to code properly.
2) talk to your / their manager about this and get them to handle #1
3) set up a regularly scheduled job to run an update.
4) fix the clients' stuff to handle NULLs
5) try a trigger on a test machine with a comparable load as production to see how it does - you might be surprised.
-
I'll second the suggestion 'go bash a developer across the head' (I'm a developer myself)
One thing I'd like to correct about your comments. The code inserting doesn't have to request the default, it just has to not specify a value. For example
CREATE
TABLE Test (
ID INT NOT NULL,
Second INT NULL DEFAULT 10
)
GO
INSERT
INTO Test (ID, Second)
VALUES (1,NULL)
INSERT INTO Test (ID)
VALUES (2)
GO
In the first case, NULL will be inserted into the column Second, because it was explicitly specified. In the second case, the colun Second will have the default value of 10 because no value was given.
Results:
ID Second
----------- -----------
1 NULL
2 10
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gilamonster,
Kindest Regards,
Just say No to Facebook!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply