Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger to concatenate two columns and update in new column Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 6:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:35 AM
Points: 227, Visits: 706
Hi,


i have a table with columns

ID
F_Name
L_name
Full_Name

my requirement is when user inserts id, F_name, L_name

using trigger - concatenate F_Name + Lname and update in Full_Name column.

it should occur when insert / update happens

Please help me....


Post #1363458
Posted Monday, September 24, 2012 6:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Rather than a trigger, can you change the table so that Full_Name is a calculated column?

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1363460
Posted Monday, September 24, 2012 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 13,126, Visits: 11,964
Mark-101232 (9/24/2012)
Rather than a trigger, can you change the table so that Full_Name is a calculated column?


+1

A calculated column is far better here than a trigger for a number of reasons.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1363518
Posted Monday, September 24, 2012 9:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 1,973, Visits: 2,919
Agreed.

For example:

ALTER TABLE dbo.tablename
DROP COLUMN Full_Name;

ALTER TABLE dbo.tablename
ADD Full_Name AS CAST(ISNULL(F_Name + ' ', '') + ISNULL(Lname, '') AS varchar(100))
-- change 100 to whatever max length you want for the column


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1363560
Posted Monday, September 24, 2012 2:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
ScottPletcher (9/24/2012)
Agreed.

For example:

ALTER TABLE dbo.tablename
DROP COLUMN Full_Name;

ALTER TABLE dbo.tablename
ADD Full_Name AS CAST(ISNULL(F_Name + ' ', '') + ISNULL(Lname, '') AS varchar(100))
-- change 100 to whatever max length you want for the column


It would take a little more space but I recommend adding the PERSISTED hint.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1363716
Posted Monday, September 24, 2012 5:17 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 1,973, Visits: 2,919
Jeff Moden (9/24/2012)
It would take a little more space but I recommend adding the PERSISTED hint.



For me, it depends, mostly on how and/or how often the full_name was used.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1363750
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse