Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2001
    Location
    S. Africa
    Posts
    9

    Unanswered: Referential Integrity - Sequel Server 7.0

    Hi,

    i wrote the following trigger to enforce referential integrity:

    CREATE TRIGGER [RefIntegrity] ON [Erf] <- Master table
    FOR UPDATE AS

    if update(ErfNo) or update(Township) <- Primary keys in master
    begin
    Update ErfPlan <- Child table
    Set ErfPlan.ErfNo = i.ErfNo , ErfPlan.Township = i.Township
    From ErfPlan a, Inserted i, Deleted d
    Where a.ErfNo = d.ErfNo and a.Township = d.Township
    end

    i am using VB6.0. When my program updates one of the primary keys i get an error :
    Key column information is insufficient or incorrect. Too many
    rows were affected by update.

    i have designed the ErfPlan table with a RcdID field (Autonumber).

    This error ONLY occurs on existing records. If i create a new master and related child record, and update any of the key fields, VB displays no error!

  2. #2
    Join Date
    Dec 2001
    Location
    S. Africa
    Posts
    9
    the funny thing about the vb error: if i requery the record that had a key value/s changed, i can see that it has been updated (for one-to-one AND one-to-many relationship). what i do not understand is why vb displays the error (Key column information is insufficient or incorrect. Too many rows were affected by update).
    this error is only displayed when a master record has a one-to-many relationship, but not in a one-to-one relationship.

Posting Permissions

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