Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Unanswered: Update trigger, multiple rows, non static primary key

    Hello,

    Anyone know how to create an update trigger where the primary key isn't fixed.

    If the primary key change how can I tie together the Deleted and Inserted tables if more than one row is updated?

    /Patrik

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What do you mean the "primary key isn't fixed"?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2007
    Posts
    4
    The column that is the primary key can be updated.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would suspect whatever you're using for a primary key is not an appropriate candidate if it can be updated. Can you describe a bit about the business rules surrounding your decision?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jul 2007
    Posts
    4
    I didn't personally design the table so I'm afraid the decision wasn't mine.

    The table represents projects and the key projectID is selected by a user. The projectID's are chosen by project leaders and can't be generated by an identity or guid column. They will probably never change but if something drastic happens it would be nice to have the possibility.

    Any how the table which is attached below is referenced by 3 tables and a few web pages so it would mean some work to add a surrogate key. Id prefer if I could just write the trigger, there won't be a lot of updates anyway so I'm not worried about performance loss.

    CREATE TABLE Project(
    projectID] nvarchar(10) PRIMARY KEY,
    Name nvarchar(30),
    UserName nvarchar(30),
    Date datetime ,
    Default bit,
    SortOrder nvarchar (3) NULL
    )

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Hulk_Hogan
    Any how the table which is attached below is referenced by 3 tables and a few web pages so it would mean some work to add a surrogate key.
    You should be able to add a surrogate key and your applications not change one jot in how they interact with the database. They just continue to use the natural key.

    I presume there are no other candidate keys? Name for example?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds like a classic we had.
    Every employee was assigned a unique identifier... That payroll would change!

    The solution: We added a "proper" primary key to the table and changed employee number to be an attribute (just the same as someone's name, dob, etc) which could change at will. We did of course add the constraint that it had to be unique, but no other tables relied on this attribute.
    George
    Home | Blog

  8. #8
    Join Date
    Jul 2007
    Posts
    4
    Didn't realize that you could reference candidate keys. That will do the trick for me, thank you.

    Just out of curiosity, is it impossible to write such a trigger.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Hulk_Hogan
    Didn't realize that you could reference candidate keys.
    Of course. Just make sure you have a unique index on all of them.

    Quote Originally Posted by Hulk_Hogan
    Just out of curiosity, is it impossible to write such a trigger.
    Yup.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Yup.
    I mean if there is no fixed link between the old version of the row and the new one.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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