I don't expect that multi-row updates will be the norm; I do anticipate that
most of these updates will be single-row, but, assuming I would allow
multi-row updates, and assuming, as these posts indicate to my feeble mind,
all relation is lost between deleted and inserted if a segment of the
foreign key is changed (and cascade isn't possible), then couldn't I take
the long approach of deleting child records that match the deleted table,
and inserting all records from the inserted table into the child? Of course
this is not what I wanted to do, but I believe it would work.
Also, I don't believe that the problem is in the design of the database. If
I accept the referential integrity limitations of this table relation, then
I can do a bit of enforcement in the front-end application. The master
table is the protocol. When a subject enters the protocol, I am creating a
personalized copy of the protocol in the child table. I am able to prevent
the user from manually deleting or modifying records that match protocol
records, but I do not prevent the user from inserting "non-protocol" visits
or "non-protocol" or additional tests within protocol visits.
The problem presents itself when, after subjects are entered, the master
protocol changes, and the corresponding subject visits (and only those that
haven't already been performed) need to be updated accordingly. As I said,
this master update will probably (99.9%) be done as a single-row update, eg.
"Change TestA to TestB for visit 2", which is no problem for the trigger.
But I can anticipate multi-row updates in certain instances, e.g. "Change
every TestA to TestB."
Also, the assumption that protocol visits will be integer values and
non-protocol will be fractional is incorrect. I apologize for leading you
to that in my example. Although I encourage users to use discreet integer
values when numbering visits, they are free to assign any valid number
within the protocol.
"Steve Kass" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...