Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2002
    Posts
    8

    Unanswered: 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.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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.

  3. #3
    Join Date
    Feb 2002
    Posts
    8
    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

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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.

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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.

    Code:
    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

  6. #6
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Unfortunately that only works for single row inserts.
    And assumes an ID on the table.
    Apart from that is the same as my trigger.

  7. #7
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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.

  8. #8
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Sorry - just thought I'd point out a problem, which is quite common, with the trigger you posted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •