auto increatment

how can i do that in mssql ?
e.g. existing data
Column 1, Column 2
A 001
B 001
B 002
C 001
then Column 1 and Column 2 are primary key.
how can i set the column propertis for column 2 such that i can get
auto-number B003 while entering B, and auto-number C002 while entering C ?calvin wrote:

> e.g. existing data
> Column 1, Column 2
> A 001
> B 001
> B 002
> C 001
> then Column 1 and Column 2 are primary key.
> how can i set the column propertis for column 2 such that i can get
> auto-number B003 while entering B, and auto-number C002 while
> entering C ?
Create a trigger to do this, you cannot do it with standard Identity
column.
HTH,
Stijn Verrept.|||can u give a sample trigger thx ?|||Hi
I'd prefer to write a stored procedure rather using triggers
Look at my exmple does the job for you
CREATE TABLE #Test
(
row_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
col1 CHAR(1),
col2 VARCHAR(30)
)
DECLARE @.par CHAR(1),@.err INT
SET @.par ='A'
BEGIN TRAN --If an insertion is failed rollback entire transaction
INSERT INTO #Test (col1)VALUES (@.par)
SET @.err =@.@.ERROR
IF @.err >0 ROLLBACK TRAN
UPDATE #Test SET col2=(SELECT '00'+CAST(COUNT(*)AS VARCHAR(3))
FROM #Test WHERE col1=@.par)
WHERE row_id =(SELECT MAX(row_id) FROM #Test WHERE col1=@.par)
SET @.err =@.@.ERROR
IF @.err >0 ROLLBACK TRAN
COMMIT TRAN
SELECT * FROM #Test
"calvin" <admin@.newsgroup.com.hk> wrote in message
news:ua1GgFvBGHA.3980@.TK2MSFTNGP14.phx.gbl...
> can u give a sample trigger thx ?
>|||calvin wrote:

> can u give a sample trigger thx ?
CREATE TRIGGER SetID ON [dbo].[Table]
FOR INSERT
AS
Update Table set Column2 = (select IsNull(max(TB2.Column2), 0) + 1 from
Table TB2 where INS.Column1 = TB2.Column1)
from Table TB inner join inserted INS on INS.Column1 = TB.Column1
By using a trigger you are sure that this is handled whatever way data
is inserted into your table.
HTH,
Stijn Verrept.|||>> then Column 1 and Column 2 are primary key.
If both columns form the key, you'd want to make sure there is no dependency
between one column to another. So the question becomes, why would you want
to do something like this?
Anith|||Here's what I did:
1. Primary keys do not allow null values in any of it's columns. Therefore,
I had to add another column, assign identity value and make it the primary
key.
2. I had to allow null values into column 2 as you cannot insert a row with
null value otherwise. (SQL Server checked that first before running the
insert trigger.)
3. I could not get the trigger posted by Stijn to work. (It could be
something I was doing wrong.) Plus, it looked to me that his trigger would
update all rows with the new value.
4. It looked like you wanted character-based sequence numbes (i.e. 001, 002,
003, etc.) so I added code to zero pad it to 6 characters (i.e. 000001,
000002, etc.). If you don't want that, simply remove the right padding and
converting to varchar.
Here's my version of the trigger that is tested in SQL Server 2000.
CREATE TRIGGER SetID ON dbo.[Table]
FOR INSERT
AS
update [Table]
set Column2 = (select right('000000' + convert(varchar,
IsNull(max(TB2.Column2), 0) + 1), 6)
from [Table] TB2 where TB2.Column1 = INS.Column1)
from [inserted] INS
where [Table].Column1 = INS.Column1
and [Table].Column2 is null
Hope that helps,
Joe
"calvin" wrote:

> how can i do that in mssql ?
> e.g. existing data
> Column 1, Column 2
> A 001
> B 001
> B 002
> C 001
> then Column 1 and Column 2 are primary key.
> how can i set the column propertis for column 2 such that i can get
> auto-number B003 while entering B, and auto-number C002 while entering C ?
>
>