Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    3

    Unanswered: Trigger or Stored Procedure Question

    Persons Table
    PersonID int NOT NULL 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 NULL

    Spouses Table
    SpouseID int NOT NULL PRIMARY KEY
    HusbandID int NOT NULL FOREIGN KEY Persons(PersonID)
    WifeID int NOT NULL FOREIGN KEY Persons(PersonID)

    Persons Table Data
    PersonID PersonFatherID PersonMotherID PersonGeneration PersonFirstName PersonLastName
    1 1 1 1 Adam Smith
    2 2 2 1 Evelyn Smith
    3 1 2 2 Caleb Smith
    4 NULL NULL 0 Sara Jones

    Spouses Table Data
    SpouseID HusbandID WifeID
    1 1 2
    2 3 4

    I want to update PersonGeneration column in Persons table when that person is added into Spouses table. For example, if Sara Jones is added as wife to Caleb Smith in Spouses table, then it should update Sara's PersonGeneration column (0) with Caleb's PersonGeneration (2) in Persons table. So, the rule is if PersonFatherID=NULL and PersonMotherID=NULL and PersonGeneration=0, then update this person's PersonGeneration with his/her spouse's PersonGeneration. I am thinking about a trigger or a stored procedure or both. Any help would be greately appreciated.

    Thanks,
    Mohan John

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why does your spouses table have a SpouseID?

    Is personGeneration something that is calculatable?
    i.e. given someones fatherID, motherID and generation can you work it out?

    If so, do you really have to store it?

    Speaking of generation - what does that exactly mean, how is it worked out? That's probably niaivity on my part
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, this just looks like a bad design.

    Forget that fact that it will totally crash if you try to model, say, some of the familial relationships you might encounter in Kentucky...unless you plan to seed your database with Adam and Eve as the original records you should be calculating generation dynamically as it is always in relation to another person.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2008
    Posts
    3
    Quote Originally Posted by georgev
    Why does your spouses table have a SpouseID?

    Is personGeneration something that is calculatable?
    i.e. given someones fatherID, motherID and generation can you work it out?

    If so, do you really have to store it?

    Speaking of generation - what does that exactly mean, how is it worked out? That's probably niaivity on my part
    SpouseID is actually couple's id. I need it because I want keep track of a person's children in multiple spouses. Anyway, do you have any suggestions how to write a stored procedure or trigger to update my original question?

    Thanks,
    Mohan John

  5. #5
    Join Date
    Jan 2008
    Posts
    3
    Quote Originally Posted by blindman
    OK, this just looks like a bad design.

    Forget that fact that it will totally crash if you try to model, say, some of the familial relationships you might encounter in Kentucky...unless you plan to seed your database with Adam and Eve as the original records you should be calculating generation dynamically as it is always in relation to another person.

    The reason I want to update the generation to spouse's generation because the generation 0 spouse might be coming from a different family and I have no way to put that spouse's parents in the current family tree. So, the FathereID and MotherID of the spouse coming from a different family are NULL. Therefore, there is no way to find out the generation of that spouse, that's why I want to update with the other spouse's generation. This will work for my family, because I haven't traced any marriages between 2 different generations in our family. Anyway, do you have any SQL code to update the problem in my original question?

    Thanks,
    Mohan John

Posting Permissions

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