column properties - default or formula

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

SQL Server Development (2000)

column properties - default or formula

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-02 : 08:18:12

Pilar writes "Hi Folks

I am trying to setup a primary key with a default prefix of 'EQ' for 'Equipment' or 'TN' for 'Therapy Needs' - these are two seperate lookup tables.
Is there an easy way of keeping the prefix, but automatically incrementing the number, e.g. EQ001, EQ002, EQ003, etc. or TN001, TN002, TN003 ?
Just in case you are wondering - there is a valid business reason for doing things this way !!

Also, can this be done in the 'Default' or 'Formula' properties of the column, or should this be done programmatically behind the form ??

I am quite new to SQL Server - going on training at the end of the month, but need to figure stuff out in the meantime.

Looking forward to your response.

Adios
Pilar"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-02 : 10:46:02

A trigger might be a good option here.
Something like this..

Create table keytest (
myid char(10) NOT NULL PRIMARY KEY,
prefix char(2),
myval varchar(100)
)

GO

Create Trigger Keytestid
ON keytest
INSTEAD OF INSERT
AS

INSERT INTO keytest (myid, prefix, myval)
SELECT
CAST(myid as char(2)) +
CAST(
( SELECT Count(*) + 1 FROM keytest WHERE prefix = inserted.myid)
as char(8)),
myid, myval

FROM
inserted

GO

INSERT INTO keytest (myid, myval)
VALUES ('TN', 'test1')
INSERT INTO keytest (myid, myval)
VALUES ('TN', 'test2')
INSERT INTO keytest (myid, myval)
VALUES ('EX', 'test3')
INSERT INTO keytest (myid, myval)
VALUES ('EX', 'test4')

GO

SELECT * FROM keytest

Damian
Ita erat quando hic adveni.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources