Is there an easy way? - 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

General SQL Server Forums

New to SQL Server Programming

Is there an easy way?

Author

Topic

tmcivery
Starting Member

46 Posts

Posted - 2011-02-23 : 00:23:23

To concatenate the primary key field in a table with a value and place that whole value into another field? I'd like this to be done automatically when a new row in the table is created.

Example:

'TN-" + table.Recid

This value would be placed in table.newfield.

Any help would be appreciated. I'm pretty new to this stuff.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-23 : 00:37:39

Yes. You can add a calculated column.

ALTER TABLE dbo.Table1 ADD MyNewColumn AS ('TN' + CAST(RecID AS VARCHAR(12))) {persisted }

N 56°04'39.26"
E 12°55'05.63"

tmcivery
Starting Member

46 Posts

Posted - 2011-02-23 : 09:16:13

Thanks for that!

This is more of a "i wonder" type question but can you think of a way to do this via a trigger?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 10:36:31

yes, absolutely you could do that with an INSERT TRIGGER but why bother? Peso's way is better.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

tmcivery
Starting Member

46 Posts

Posted - 2011-02-23 : 11:06:34

The reason I'm asking that is because we need to use an existing field to place the calculation in rather than create a new one. Is there a way to alter an existing field to hold that data or can I get some pointers on a trigger that would work? I'm going to try to work out a trigger myself but I'm sure i'm going to end up needing some guidance.

tmcivery
Starting Member

46 Posts

Posted - 2011-02-23 : 11:39:18

Ok, I've come up with this. It works fine doing one off inserts within the CRM product that I've created it for. Does anyone see anything wrong or that should be changed? Looking for any constructive criticism if it's necessary.

Create TRIGGER InsertTrigger On Contact1
After INSERT
AS
BEGIN
SET NOCOUNT ON
Update Contact1
Set Key5 = ('TN-') + (select Recid from inserted)
Where Recid = (Select Recid from inserted)

END
GO

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-02-23 : 11:44:17

I think you have to JOIN to the INSERTED table.

update a
set a.x = y
from a
join inserted on a.key = inserted.key

tmcivery
Starting Member

46 Posts

Posted - 2011-02-23 : 11:58:58

Thanks for the input Andrew. Is this better?

Create TRIGGER InsteadTrigger On Contact1
After INSERT
AS
BEGIN
SET NOCOUNT ON
Update c1
Set c1.Key5 = ('TN-') + (select Recid from inserted)
From Contact1 as c1
join inserted on c1.recid = inserted.recid

END
GO

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 12:23:41

why not just script out the table and recreate the table with that nice computed column in place instead

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

tmcivery
Starting Member

46 Posts

Posted - 2011-02-23 : 12:44:27

Really I'm in a position where I'm "just doing what I'm asked". Recreating the entire table is not an option as far as I understand it as this is a database for a 3rd party product that I'm not really familiar with. I understand what you are saying but I'm really just going to give them what they're asking for and let it be.

I appreciate the input.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-23 : 13:18:38

I might be wrong but i always try to avoid the usage of triggers until i have no other choice, as it degrades performance ... what you are trying to achieve is a computed column and i think you don't need to store as it can be computed any time you want for any row. However still if you need it then perhaps following would be an optimal way than using trigger

Case 1: If the primary key of your table is a unique value and is explicitly entered via application then it would be better if you insert the calculated column at the time of inserting a row into your table ..
e.g.

Insert into TableName (Col1,Col2,Col3,...ColN,ComputedColumn)
Values (Value1,Value2,Value3,...,ValueN,'TA-'+Value1(which is the primary key value))

Case 2: If the primary key of your table is auto Generated still it would be better if you insert the calculated column at the time of inserting a row into your table .. e.g.

Insert into TableName (Col2,Col3,...ColN,ComputedColumn)
Values (Value2,Value3,...,ValueN,'TA-'+ltrim(str(@@identity))[/b]))

Cheers
MIK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-23 : 16:41:32

Create TRIGGER InsteadTrigger On dbo.Contact1
After INSERT
AS

SET NOCOUNT ON

Update c1
Set c1.Key5 = 'TN-' + cast(i.Recid as varchar(12))
From Contact1 as c1
join inserted as i on i.recid = c1.recid

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources