Combining two field values as the default value for another field.

Hi all,

I have a field called strFullName (SQL 7) that I wish to populate by combining the fields strFName and strLName separated by a space.

Do I do this with ALTER TABLE ALTER COLUMN??

If so, where do I create this statement and what is the syntax I should use. BOL suggests using this method, but I know so little about the language and physical db structure that I can't figure out where to begin.

Thanks,

A
headbumpAsked:
Who is Participating?
 
namasi_navaretnamCommented:
Try this,

CREATE TRIGGER tiu_tblEngineer
ON tblEngineer
FOR INSERT, UPDATE
AS
BEGIN

If  EXISTS (Select 1 from Inserted)
BEGIN
   Update tblEngineer
     Set strFullName = i.strLastName + ', ' + i.strFirstName
   FROM inserted i
   WHERE tblEngineer.intEngineerID = i.intEngineerID
END

END
0
 
namasi_navaretnamCommented:
You can use insert and update triggers to accomplish this.

CREATE TRIGGER tiu_Table
ON Table
FOR INSERT, UPDATE

If Exists(Select 1 from Inserted)
BEGIN
   Update Table
     Set FullName = i.LastName + ', ' + i.FirstName
   FROM inserted i
   WHERE Table.pk_id = i.pk_id // pk_id is the primary key
 

END

GO
0
 
headbumpAuthor Commented:
NAMASI_NAVARETNAM,

Thanks for the response...I've tried your suggestion but get an error when I do this:

CREATE TRIGGER tiu_tblEngineer
ON tblEngineer
FOR INSERT, UPDATE

If  EXISTS (strLName)
BEGIN
   Update tblEngineer
     Set strFullName = i.strLastName + ', ' + i.strFirstName
   FROM inserted i
   WHERE tblEngineer.intEngineerID = i.intEngineerID



Incorrect syntax near the keyword If
Incorrect syntax near intEngineerID

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
You should use a computed column *instead*, like so:

ALTER TABLE yourTableName
ADD strFullName AS strFName + ' ' + strLName

Reasons:
1) You avoid the overhead of a trigger
2) The new column is not stored in the table, so does not take any disk space
3) You can reference the new column name in any SELECT query just as would an actual column name
4) If necessary, you can change the column definition in one place and it will immediately take effect, with no other UPDATEs being required.

0
 
DexstarCommented:
I was going to suggest that you use a view that displays the combined values, but Scott just totally schooled me.  Geez!  I didn't even know you could do that!

Scott, I'm very impressed.

My $0.02,
Dex*
0
 
Scott PletcherSenior DBACommented:
Thanks Dex*; yeah, virtual fields are pretty slick, and it's great that MS added them.
0
 
richard_westCommented:
Scott --

Can you clairify about how the new computed column does not require any addtional disk space?

Also, can you index on the computed column?
0
 
Scott PletcherSenior DBACommented:
With certain reasonable restrictions you can, and in this case I'm almost certain you could.  See "Creating Indexes on Computed Columns" in BOL for more details.

It doesn't take up space because it's not actually derived until and unless you reference it in the query.  We know that SQL can "materialize" a new column as part of a query, that is:

SELECT colA + colB AS colC
FROM ...

well, this is essentially the same thing except that the expression has been pre-defined to SQL Server.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.