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:

Create a trigger to do this, you cannot do it with standard Identity
column.

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

Show quoteHide quote

calvin wrote:

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.

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?

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

Show quoteHide quote

Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation
Deadlock updating different rows
No retrieving dupicate rows