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?
:mad:
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!!!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|||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.|||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|||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?|||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