Unanswered: Referential Integrity - Sequel Server 7.0
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
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
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!
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.