Default - to another column - 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 2000 Forums

Transact-SQL (2000)

Default - to another column

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-01 : 10:51:48

Is it possible to have a column's value default to another column's value (without a trigger) ?

E.G. ipso/facto, ergo:

INSERT INTO MyTable (ColA)
SELECT 5

I'd like ColB in MyTable to assume the value of ColA since none was specified on insert.

Sam

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-01 : 14:19:51

Computed columns:

create table #MyTable( ColA int, ColB AS ( ColA ) )

insert into #MyTable( ColA ) select 5

select * from #MyTable

drop table #MyTable

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-09-01 : 14:31:58

The definition of a default value is a value applied upon creation of a line item.
Now if the line item is not yet created how can it reference the other column.

Sounds like the chicken and egg theory to me Sam.

Jim
Users <> Logic

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-01 : 14:56:56

I'm convinced it can't be done without a trigger. I had so hoped not to start writing triggers.

Rockmoose. Sometimes I need the column to be another value. I can't change a computed column, and I hoped there might be a way to specify a computed column as a DEFAULT value.

I can handle specifying the field without a DEFAULT to another column's value. I'd never had a requirement for this and I was thinking it would be cool to have a computed DEFAULT.

Jim. I don't know what to say.

We could argue the posted values are available so the default could be computed before writing into the table. OR We could have a margarita.

Sam

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-09-01 : 14:59:14

I will have a VO & 7 Please and it sounds like you will have a trigger.

Jim
Users <> Logic

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-01 : 15:08:35

Quick. Someone post a recipe for an "SQL Trigger".

Make it strong.

2 Parts Vodka
2 Parts Tequila
and ...

Help me here Brett.

Sam

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-09-01 : 15:16:45

SQL Trigger

4 rusty paper clips
2 post-it notes
1 bottle of white out
The inside felt from 1 green highlighter
1 crushed CD-R
2 Quarts grain alcohol

Stir only
(Blows up if shaken)

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 15:44:50

quote:
Originally posted by JimL

SQL Trigger

4 rusty paper clips
2 post-it notes
1 bottle of white out
The inside felt from 1 green highlighter
1 crushed CD-R
2 Quarts grain alcohol

Stir only
(Blows up if shaken)

Jim
Users <> Logic

You crack me up

And you won't believe this...

http://www.mattonigranddrink.com/cocktail.php?id=1431

Oh, and


CREATE TABLE myTable99(Col1 int, Col2 int DEFAULT(Col1))

quote:

The name 'Col1' is not permitted in this context.
Only constants, expressions, or variables allowed here.
Column names are not permitted.

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-01 : 16:09:09

Yeah Brett. I tried that too.

Hic !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources