Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    7

    Unanswered: Use Trigger/Stored Procedure for updating primary key

    Hello,
    I have a central customer info table which has 2 columns - one is a record number which is an identity field and another which is an ID for the customer.

    The record number works as a unique identifier for the info table.

    The ID works as a unique identifier for the customer itself. Unfortunately, I have already created a lot of other tables (And have added data to them ) which use this ID as a foreign key. Now I cannot go back and change the data, it would be a very tedious process.

    What I am trying to do is to create an increment table which stores the latest customer id record and then use a stored procedure or a trigger to
    1) check the LAST_UPDATED value in the increment table and add a new customer record with an ID : LAST_UPDATED+1
    2) Write back the new value to the increment table.

    IS there someone who can help me get started on how to get this going? Unfortunately, I do not have the liberty to change the business application (web based) code either to either call the stored procedure or check the LAST_UPDATED value in the increment table, I have to do this purely by trigger and stored procedure.

    Any help would be greatly appreciated!

    Thanks!
    Rakesh.

    PS: Sorry! I just checked the forum posting guidelines - I am using SQL Server 2005
    Last edited by rakesh.mathur; 08-24-09 at 21:19.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    As things are what they are and your whole database uses the Customer ID as PK, why not drop the whole notion of the Record Number as PK and go with the Customer ID as PK? Drop the now useless Record Number and declare the Customer ID as PK.

    Even if you want to persist, I don't see what you would need that increment table for. How and when would you use it?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Mar 2009
    Posts
    7
    Wim,
    Thanks very much for your reply. You are right - there is no need for the Record Number.

    Let me restate what I want to do:
    1) I want to use Customer ID as a unique identifier for the Customer. This ID will be used as a Foreign Key in related tables. This customer ID needs to be
    a) auto generated when a new record is added and b) needs to be unique.

    But there is a problem which is that I do not want to have the Customer ID sequentially generated.

    The increment table would basically make sure that it saves the last Customer ID created and then increments it (adds a 2 or 1) so that the next one which is created is unique.

    Also this allows me to upload bulk data with out of order Customer IDs, as long as the largest value is stored in the increment table, the next one created will be unique.

    Does this make sense?

    Rakesh.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you make an IDENTITY column from the Customer Id ? It would solve all your problems.

    Copy the data from the customer table to a temp table.
    Recreate the table with the ID as IDENTITY. Suppose the highest current value of ID is 14897, create it with a starting value greater than the max current value, e.g. 15000.
    Code:
    CREATE TABLE customer(
       ID     BIGINT     IDENTITY (15000, 1) ,
       ...,
       CONSTRAINT pk_customer PRIMARY KEY (ID)
    )
    Use SET IDENTITY_INSERT dbo.customer ON
    before copying all the values of the temp table to dbo.customer.
    Then use SET IDENTITY_INSERT dbo.customer OFF

    When you insert a record, the ID's will start from 15000 and increment by 1.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Mar 2009
    Posts
    7
    Wim
    Thanks for your reply. Just one thing - when I copy the records from the temp table to my customer table, will it reset the ID and make them all sequential?

    Rakesh

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by rakesh.mathur
    when I copy the records from the temp table to my customer table, will it reset the ID and make them all sequential?
    No.

    Normally it is not possible to supply the ID when it is defined as IDENTITY. SQL Server will generate them for you, automatically.

    When you issue the
    SET IDENTITY_INSERT dbo.customer ON
    command, it will be possible to supply the (old) ID (from the temp table).
    Once the old records are restored, issue the
    SET IDENTITY_INSERT dbo.customer OFF
    command to let SQL Server auto generate new IDs. In this example the new IDs will start from 15000, the next 15001, 15002, .... incrementing by 1.
    Code:
    SET IDENTITY_INSERT dbo.customer ON;
    
    INSERT INTO dbo.customer (ID, column1, column2, ...)
    SELECT ID, column1, column2, ...
    FROM TempTable;
    
    SET IDENTITY_INSERT dbo.customer OFF;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Mar 2009
    Posts
    7

    Thumbs up

    Wim,
    This helps a lot! Thank you very much for explaining this.

Posting Permissions

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