September 4, 2006 at 6:35 am
How would I write an insert trigger on a table "Person" whose PK is PartyID (not identity column). It obtains the value of that PK from table Party who inserts a new record (PartyID, identity column). I need to furnish that back to the Person table. What is the standard methodolog for handling this? (from a newbie).
Sam
September 4, 2006 at 7:14 am
I think you're going backwards on this. You normally insert the data in the parent table, fetch the new identity value. Then use that to insert in the child table like so :
Declare @NewPK as int
Insert into dbo.Party (Col1, Col2) values ('Val1', val2)
SET @NewPK = Scope_identity()
insert into dbo.Persons (NewIden, col1) values (@NewPK, 'Whatever')
September 4, 2006 at 7:15 am
One last thing I'm wondering is why is there a refference from Persons to party like that?
It's my understanding that normally a person will attend more than one party in its lifetime. However your design doesn't allow for that. Can you provide more details on what you are trying to do to make sure you are on the right path?
September 4, 2006 at 7:53 am
In this case, "Party" means any entity that can have an address or enter into a contract. A party can be either a person or organization. I prefer to keep the person table separate from the organization table, but need a unique PartyID whether the entity be a person or an organization. I have a table Party with a single column, PartyID that generates the PKs for either table. But the implementation of this is giving me headaches.
This is my source info for the model I am trying to implement:
http://www.dmreview.com/article_sub.cfm?articleId=5675
Sam
September 4, 2006 at 8:17 am
I see now. Were you able to make my demo code work for you? Are do you have any more questions?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply