Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    87

    Unanswered: Get Primary Key from another Table

    Hello everyone,
    I've just tried realizing following project:

    Currently i have separate Tables with separate Primary Keys (ascending separate Number regions).

    Now I want to do the following:
    Table - NewID
      Field - ID - Identity
      Field - IDproc - Sub-Data Number

    Table - Data1 (IDProc=1)
      Field - ID - Primary Key, generated NEW from NEWiD

    Now great would be if I could add a new Recordset in Data1 and I could generate a New ID in 'NewID' trough a Trigger and Set it on the inserted Data, but that doesn't seem possible.

    I was able to do it with the following trigger:
    CREATE TRIGGER [getNewID] ON [dbo].[Data1]
    FOR INSERT
    AS
    DECLARE @newID int

    INSERT INTO NewID (IDProc) VALUES(1)
    SELECT @newID=(SELECT TOP 1 id FROM NewID WHERE IDProc=1 ORDER BY ID DESC)
    UPDATE Data1 SET ID=@newID WHERE ID IS NULL

    But as I can imagine this would get some problems if two users insert a Recordset at the same Time - right?

    Is there somehow the possiblity to 'SET INSERTED.ID = @NewID' ??, because it's the only real ID I have in this table (beneath a TimeStamp wich I also couldn't use).

    Thanks for any Help!!!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.

    Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.

    blindman

  3. #3
    Join Date
    Jul 2002
    Posts
    87
    Originally posted by blindman
    First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.

    Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.

    blindman
    Hi Blindman
    NewID was just a variable for my thoughts, i don't use the function NewID - this is just the tablename and var-name of the trigger...

    The idea is to have a main table with all the ID's and sub-data information so I can have a table Data1 Data2 and Data3 wich dont get themselves into the way with their ID's.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.

    blindman

  5. #5
    Join Date
    Jul 2002
    Posts
    87
    Originally posted by blindman
    If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.

    blindman
    Wow, i've just took a look at this NewID function, that's a pretty big thing :-).
    I would be pretty happy if i could handle the ID with numbers, not that a customer has to spell me those 20 chars , do you know any way I could do this trough triggers?
    Or is there a possibility to convert the NewID-var to some kind of number?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    NewID produces a standard UniqueIdentifier data type which can be implicitily converted to a char(36) or nchar(36) data type. It's good practice to make your IDs invisible to Users anyway and keep them for internal data processing.

    blindman

Posting Permissions

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