Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-16 : 07:03:55
|
James Dummett writes "I've read alot of articles here and elsewhere on the web about identity columns and @@IDENTITY but none really answer the question that I have.
The database I'm working on is on SQL Server 7 and has several tables to contain details of different types of entity (in this example tblPeople and tblGroups). Each row in any of the tables needs to have an ID that is unique not only within its own table but across all the tables. There is a further table tblObject which has a row in it for each row in any of the other tables, and keeps track of all the Objects.
The problem comes when inserting new rows into eg tblPeople- the IDs need to come from tblObject. My current solution involves:
tblObject has 3 columns:
ID INT IDENTITY(1,1),
TempID INT,
Type CHAR(6)
and each of tblPeople and tblGroups has 3 columns:
ID INT,
TempID INT IDENTITY(1,1),
Name VARCHAR(50)
When records are inserted into tblPeople, it fires a trigger which copies the TempIDs into tblObject and for each of these rows sets the Type to 'People':
-----
CREATE TRIGGER trInsertPeople ON tblPeople
FOR INSERT
AS
INSERT INTO tblObject (TempID, Type, Name)
SELECT TempID, 'People', Name
FROM INSERTED
-----
This in turn fires the trigger on tblObject which then copies the ID column back to the relevent records in tblPeople:
-----
CREATE TRIGGER trInsertObject ON tblObject
FOR INSERT
AS
UPDATE tblPeople
SET tblPeople.ID = INSERTED.ID
FROM INSERTED
WHERE tblPeople.TempID = INSERTED.TempID
AND INSERTED.Type = 'People'
UPDATE tblGroups
SET tblGroups.ID = INSERTED.ID
FROM INSERTED
WHERE tblGroups.TempID = INSERTED.TempID
AND INSERTED.Type = 'Groups'
-----
This solution works but it's clumsy- the neccesary inclusion of TempID columns in all the tables which are, apart from this process, completely useless.
Is there a better way?
Cheers,
James Dummett" |
|
|
|
|
|
|
|
Using a unique ID across multiple tables