Without knowing more, I really can't say what to do. I don't know what
"completed" means, and I don't know how it makes sense to simply delete
records from the child table and then insert records into that table from
inserted, which I thought was from an update on the parent table, not the
child table. If the child table contains information that is not present in
the parent table, aren't you losing important information and replacing it
with generic default values?
> 1. No, I do not have to change the 2345. If it is already there, fine.
> the child has both 101 and 2345, and 101 is documented as "complete", it
> should stay, otherwise it should be deleted. Doesn't matter what the
> of 2345 (complete or not) is.
> 2. This is exactly the situation in the original post. In this case 101
> and 102 are in the deleted table, and 202 and 302 are in the inserted
> but which record in deleted became which in inserted? The only way I have
> thought of to handle this is to remove child records that have not been
> completed that match the values in deleted, then to insert into the child
> table records from inserted that do not have a match in the child table.
> 3. They can use any number (for the visit) that does not violate the
> primary key in the child table. The application determines the last
> completed visit and offers some decimal value between it and the next
> e.g. if between 2 and 3, then offer 2.1. The user can accept this or
> their own value for the non-protocol visit.
> The visit numbering scheme for a protocol can be whatever the data entry
> person decides works best. In some cases there is a central reference
> that may be referred to as visit 0. Visits that precede this may be
> numbered -2, -1, etc., and visits that follow may be numbered 1, 2, 3. It
> may vary from study to study, and it really doesn't matter how one
> numbers its visits compared to another protocol.
> I'm primarily concerned with your case #2. These other issues are for me
> adiaphora. The application has been working in an Access environment for
> years using the data structures and referential integrity that Access
> provides and that I'm trying to emulate in SQL Server. If we get deeper
> into this I'd be happy to provide the DDL for about 7 of the 95 tables in
> the app, just those that pertain to this situation.
> "Steve Kass" <sk...@drew.edu> wrote in message
> > Joe,
> > I still don't understand. Here are things I think will not work,
> > my assumptions:
> > 1. update of a master primary key from 101 to 2345, where 2345 exists
> > already in the child table but not in the parent table. Do you have to
> > change the 2345 in the child table to a new value that doesn't exist in
> > parent table?
> > 2. update of two master primary keys 101 -> 202 and 102 -> 302. If
> > records exist for both 101 and 102, there is no way to tell from the
> > inserted and deleted tables how to update the child table. Should the
> > become 302 and the 102's become 202 or vice versa? You can tell this
> > the update query, but not from what's available to a trigger.
> > 3. A user inserting a row into the child table seems to have a choice
> > whether to have the child record refer to a particular parent record or
> > How do they know what numbers they can use for rows not referring to the
> > parent table?
> > Obviously I don't understand this partial referential integrity
> > your comments like "any number within the protocol - is "within the
> > protocol" anything maintained in tables?), so perhaps if you could post
> > table structures and sample data that includes all the variations along
> > the kind of updates you need triggers for and what results you want, it
> > would help.
> > SK
> > "Joe Clarke" <j...@integra-sys.com> wrote in message
> > news:O5EZlQc5DHA.1804@TK2MSFTNGP12.phx.gbl...
> > > Steve,
> > > I don't expect that multi-row updates will be the norm; I do
> > 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
> > > the long approach of deleting child records that match the deleted
> > > 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
> > 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
> > > table is the protocol. When a subject enters the protocol, I am
> > 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
> > > 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
> > > 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
> > eg.
> > > "Change TestA to TestB for visit 2", which is no problem for the
> > > But I can anticipate multi-row updates in certain instances, e.g.
> > > 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
> > > to that in my example. Although I encourage users to use discreet
> > > values when numbering visits, they are free to assign any valid number
> > > within the protocol.
> > > Joe
> > > "Steve Kass" <sk...@drew.edu> wrote in message
> > > news:%23Hsx41b5DHA.2732@TK2MSFTNGP09.phx.gbl...
> > > > Joe,
> > > > If this is the case, it sounds to me as though you can't allow
> > multi-row
> > > > updates of the parent table's primary key, since if you do, there's
> > way
> > > > to know how to update the child table.
> > > > To be honest, I think you should redesign your database. From
> > > little
> > > > you've said, you can enter any value into child.visit. If that
> > > > happens to be a parent.visit value, it means there is a certain
> > > > relationship, but if not, it's still a permitted value, but there is
> > > > relationship. I don't know how you enforce data integrity of any
> sort -
> > > if
> > > > someone commits a typo, you suddently have an "off-protocol" visit
> > > it
> > > > wasn't intended, or a related visit that shouldn't have been.
> > > > If all the non-protocol visits are decimals with a non-zero
> > > > part (wild guess from your example), then the fractional part of the
> > > decimal
> > > > has an independent meaning in your model, and should be a separate
> > column
> > > in
> > > > the database. Each non-protocol row of the table, if it does belong
> > > the
> > > > same table as the other rows, would then have a NULL in the foreign
> > > > column if it is not related to a parent record.
> > > > SK
> > > > "Joe Clarke" <j...@integra-sys.com> wrote in message
> > > > news:O0HiWdb5DHA.1948@TK2MSFTNGP12.phx.gbl...
> > > > > To all,
> > > > > Thanks for the replies. My internet connection went down 5
> > > after
> > > > I
> > > > > submitted this post, and I just got back online.
> > > > > The reason I can't use cascading update is because I cannot
> > > > > referential integrity. The child table can have values in the
> > > > column
> > > > > that the parent doesn't have. For instance, master may have
> > > > 1,2,3,4;
> > > > > child would also have 1,2,3,4 but may also have 1.5, 2.1, etc.
> > > are
> > > > > additional, off-protocol visits. They fit perfectly in the child
> > table
> > > as
> > > > > long as I don't force referential integrity. But I lose the
> > to
> > > > > cascade the update.
> > > > > Joe
> > > > > "Steve Kass" <sk...@drew.edu> wrote in message
> > > > > news:%23XJxx1U5DHA.2540@TK2MSFTNGP11.phx.gbl...
> > > > > > Joe,
> > > > > > Am I missing something, or can you do this simply by defining
> > > > > foreign
> > > > > > key relationship with ON UPDATE CASCADE ? If not, can you
> > > what
> > > > > you
> > > > > > are trying to do that won't be done by the cascading constraint?
> > > > > > SK
> > > > > > "Joe Clarke" <j...@integra-sys.com> wrote in message
> > > > > > news:u85Qr2T5DHA.1292@TK2MSFTNGP11.phx.gbl...
> > > > > > > Here is my situation:
> > > > > > > I