Default Column Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Default Column Value

Author

Topic

notmyrealname

98 Posts

Posted - 2009-05-20 : 13:37:21

Hi.

Could someone please tell me if i can use the value from one column (allow nulls = false) for another column's default value? I am trying to create default search paths based on a Project (prj_No) column value. I.e. 'M:\06-182\NCfiles'. This column is to remain editable so i cannot use a computed column definition. I also know that i can do this with triggers but am trying to find out if it can be simply achieved with a Default Value string. I.e. Default Value or Binding = 'M:\' + prj_No + '\NCfiles'

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 13:44:02

will it be same always? or only when you dont pass an explicit value for the second column?

notmyrealname

98 Posts

Posted - 2009-05-20 : 14:10:29

Application requires user to enter Project No and Name to create new record. After this, the values for these directory columns can change to anything. I just want them to default to a path that uses the Project No value that was initially enter by the user.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 14:23:48

then you could just make path a computed column based on Project No. something like
CREATE TABLE tablename
(prj_No varchar(..),
Path AS 'M:\' + prj_No + '\NCfiles',
...
)

notmyrealname

98 Posts

Posted - 2009-05-20 : 14:25:19

I thought computed columns couldn't be modified. Am i wrong. I'lkl check it out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 14:29:42

modified from where? thats what i was asking in beginning. is there a chance to pass an explicit value for path?

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-20 : 14:48:57

you mean something like:


create table x
( 
row_id int not null primary key,
record_id int not null default row_id
);

I don't think that's allowed.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 14:56:06

thats obviously not allowed. if op's question was that, then i think this need to be implemented using triggers.

notmyrealname

98 Posts

Posted - 2009-05-20 : 14:57:30

visakh16,

I only want this default value when the record is first created if an explicit value is not specified. Just a default value using the Project No's value in concatenation with the path. After that the user can modify the field to something else if the default value is not correct. Most of the time our directory structure follow a specific format but sometimes there are some exceptions.

notmyrealname

98 Posts

Posted - 2009-05-20 : 15:00:53

I'm fine using triggers if i have to. I was just wondering if i can create a Default Value with the same flexibility provided by Computed Columns.

Thanks for the help.

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-21 : 09:10:57

Allright, using the above example:


create table dbo.table_x
( 
row_id int not null primary key,
record_id int not null default 0
);
go
create trigger dbo.trg_table_x on dbo.table_x
after insert as begin
update x
set x.record_id = x.row_id
from dbo.table_x x
inner join inserted i
on i.row_id = x.row_id
where i.record_id = 0
end

is that what you're looking for?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources