Resetting the Identity field

I have a composite pk in a table 'table' in ms sql server. value in one field 'table.a' is fk to another table 'table1.a'
value in field table.b is a id field. i need to reset this field 'table.b' to 1 each time the 'table.a' changes.

Any suggestions.Not sure what you mean.
if table.a is part of the primary key it should never change otherwise it shouldn't be part of the primary key.
It sounds like you might want a trigger but maybe you could post an example.|||i see what you mean. I have changed it and i have a field table1.a and table1.b. both .a and .b are not in the keys, though .a is a fk to table2.a.
i need to increment .b by 1 on each input of .a where .a = 'x' (say). as soon as .a = 'y' (say) i need to reset .b to 0 and auto increment as new values for .a='y' are inserted.
hope this makes sense.
thanks|||ok
you have table1(a,b)
a is an id and you want b to be the sequence number within a?

put a trigger on the table

create trigger tr_table1_ins on table1 for insert
as
set rowcount 1
while exists(select * from table1 where b is null)
begin
update table1
set b = (select max(b)+1 from table1 t1 where table1.a = t1.a)
where b is null
set rowcount 0
go

if you only ever insert one row at a time then you can just do the update without the loop.

Another option is to put the current value for b on table2 and increment it within a transaction on inserts and use it with the insert.|||We've done something like this at our site. When we needed to know the occurence of a record, example "2 of 5". We implemented a TRIGGER like nigelrivett has suggested. To use a trigger you should JOIN with the INSERTED table to update only those records that were Inserted.

SET NOCOUNT ON
GO
CREATE
TABLE Occurrence
(
syID int IDENTITY (1, 1) NOT NULL ,
colA char(3),
colB int NOT NULL DEFAULT 0
)
GO
CREATE
TRIGGER tri_Occurrence
ON Occurrence
FOR Insert
AS

--
-- If no records were effected then return
--
IF (@.@.ROWCOUNT = 0) BEGIN
RETURN
END

UPDATE o
SET colB = (SELECT MAX(o.colB) + 1 FROM Occurrence o WHERE i.colA = o.colA)
FROM Occurrence o,
Inserted i
WHERE o.syID = i.syID

RETURN
GO

INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('B')
INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('C')
INSERT Occurrence (colA) values ('C')
GO

SELECT *
FROM Occurrence

syID colA colB
---- -- ----
1 A 1
2 A 2
3 B 1
4 A 3
5 C 1
6 C 2|||Unfortunately that only works for single row inserts.
And assumes an ID on the table.
Apart from that is the same as my trigger.|||You are one to get the last word in. I'm sorry that I replied to the posting with my answer. I felt that a person could cut and paste this and see a working example.

But I forget that once nigelrivett answers, we should lock the posting, case closed.|||Sorry - just thought I'd point out a problem, which is quite common, with the trigger you posted.