April 15, 2008 at 10:24 am
Hi all
I have a table
object_type contract_id
photo 0001-NSA001
video 0001-NSA056
blog
document
transcript
i would like to add a default value of "0000-BNU001" to the contract_id column
But i only want the default value inserted if the object_type is blog, document or transcript.
do i have to create a UDF and then bind it to the contract_id column or can it all be done in the table design view in the "Default Value or Binding" field?
Any help appreciated
April 15, 2008 at 11:04 am
Are these two columns in a larger table that has more columns with other data, or is this a table with just these two columns and five rows?
In the case of the larger table, I'd normalize it. Just have the object ID in the main table, and have the contract ID in a separate table with the object IDs in it. Then you don't have to update both columns at the same time.
In the case of a table with 2 columns and 5 rows, I'd just do an update command and not worry about a default constraint.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2008 at 11:11 am
GSquared (4/15/2008)
Are these two columns in a larger table that has more columns with other data, or is this a table with just these two columns and five rows?In the case of the larger table, I'd normalize it. Just have the object ID in the main table, and have the contract ID in a separate table with the object IDs in it. Then you don't have to update both columns at the same time.
In the case of a table with 2 columns and 5 rows, I'd just do an update command and not worry about a default constraint.
There are other columns and the table contains 10,000 + records.
I cannot normalize nor can i modify insert or update statements or stored procedures because the system is in production.
April 15, 2008 at 11:20 am
If you can't normalize it, and can't modify the code, because the system is in production, then even if you could add a default constraint to the table to handle this, it would be just as bad as modifying code.
There isn't, to my knowledge, a way to do this without changing things that will affect the database.
The simplest solution, if you can't modify the procs and such, would be a trigger. But that's really just a way to bypass modifying the procs and code, and is a bad idea for the same reasons.
Can you make the needed changes in a dev database, and roll them out using any of the standard dev-QA-production techniques?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2008 at 11:28 am
GSquared (4/15/2008)
If you can't normalize it, and can't modify the code, because the system is in production, then even if you could add a default constraint to the table to handle this, it would be just as bad as modifying code.There isn't, to my knowledge, a way to do this without changing things that will affect the database.
The simplest solution, if you can't modify the procs and such, would be a trigger. But that's really just a way to bypass modifying the procs and code, and is a bad idea for the same reasons.
Can you make the needed changes in a dev database, and roll them out using any of the standard dev-QA-production techniques?
The system was designed 3 years ago by a different team.
I think i might have to go with the trigger.
Thanks GSquared
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply