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 |
|
mohanjohn
Starting Member
3 Posts |
 Posted - 2008-01-15 : 13:15:39
|
| I would like to write a trigger which calls a stored procedure to update the PersonGeneration column in Persons table to its spouse's PersonGeneration. Any help would be greately appreciated.Rules for Persons Table:When PersonID, PersonFatherID and PersonMotherID are the same, they are the root parents of this family.When PersonFatherID and PersonMotherID are NULL, this person is from a different family. When this person is added as a spouse in Spouses table, it should update the PersonGeneration to other spouse's generation.For example: When SpouseHusbandID to 3(Caleb) and SpouseWifeID to 4(Sara) record is added in Spouses table, it should update the record in Persons table, '4 NULL NULL 0 Sara Jones', PersonGeneration column to 2 which is Caleb's PersonGeneration. Either husband or wife can come from a different family, but not both.Persons Table Structure----------------------- PersonID int NOT NULL IDENTITY(1,1) PRIMARY KEY, PersonFatherID int NULL FOREIGN KEY Persons(PersonID) PersonMotherID int NULL FOREIGN KEY Persons(PersonID) PersonGeneration int NULL PersonFirstName nchar(20) NOT NULL PersonLastName nchar(20) NOT NULLSpouses Table Structure----------------------- SpouseID int NOT NULL IDENTITY(1,1) PRIMARY KEY, SpouseHusbandID int NOT NULL FOREIGN KEY Persons(PersonID) SpouseWifeID int NOT NULL FOREIGN KEY Persons(PersonID)Persons - Data--------------PersonID PersonFatherID PersonMotherID PersonGeneration PersonFirstName PersonLastName--------------------------------------------------------------------------------------------1 1 1 1 Adam Smith <----Root Father--------------------------------------------------------------------------------------------2 2 2 1 Evelyn Smith <----Root Mother--------------------------------------------------------------------------------------------3 1 2 2 Caleb Smith <----Son of Adam and Evelyn Smith--------------------------------------------------------------------------------------------4 NULL NULL 0 Sara Jones <----Sara is from a different family,-------------------------------------------------------------------------------------------- so her PersonFatherID and PersonMotherID are NULL.Spouses - Data--------------SpouseID SpouseHusbandID SpouseWifeID---------------------------------------1 1(Adam) 2(Evelyn)---------------------------------------2 3(Caleb) 4(Sara)---------------------------------------Thanks,Mohan John |
|
|
|
|
|
|
|
Family Tree Stored Procedure