Very Computer

> inserted table and deleted table will have same rows when update trigger
> fires. deleted table will contain old values and inserted table will have
> new values. now, you may able to update the child table. it's easy to make
> relation between them if you know, how to relate two tables in the
> sqlserver. there is no special treatment need for that.

> Regards,

> Rajesh Patel

> > Here is my situation:

> > I have a "master" table that has the following PK

> > Visit    decimal(5,2)
> > Test    integer

> > I have a "detail" table with this PK

> > Subject    integer
> > Visit        decimal(5,2)
> > Test        integer

> > I cannot have enforced referential integrity between these tables, but I
> > need to be able to cascade the update of "Test" from the master to
certain
> > records in the detail table.
> > This presents no problem in a single-record update in the master.  But
> when
> > I have a multiple record update, because I have altered the primary key
of
> > master, I can't figure out how (or whether) I can relate records in the
> > deleted table to the inserted table.  BOL says that the records in
deleted
> > won't normally have matching records in the trigger table, and that
> inserted
> > is a copy of the new records in the trigger table.  Unless there is a
> > "bookmark" or recnum that can help me relate deleted to inserted, I'm
> > looking at coding the two-step process of 1) killing the detail records
> that
> > match the key in deleted and 2) inserting the records from inserted that
> > aren't found in detail.  Can anyone confirm that this is my only option
> (for
> > a multi-row update where the PK changes)?

> > I'm trying to get more info on the IF UPDATE(column) to see if anything
> can
> > be done here.  I'm assuming at this point that the UPDATE(column) will
be
> > true for every record in deleted, even if the value was not really
> changed.
> > For example, "UPDATE Addresses SET State = 'OH'" would update every
record
> > in Addresses, even those whose State was already "OH".  So IF
> UPDATE(column)
> > is not something I can test on a row-by-row basis, to see if the value
in
> > the column was truly changed.  Again, can anyone confirm?

> > TIA,

> > Joe

>   If the column being changed is the one the child table refers to, I
don't
> think you are correct.

> Suppose this is the update:

> update ParentTable set
>   primary_key =
>     case when primary_key = 101 then 1883726
>          when primary_key = 102 then 498378
>          when primary_key = 103 then 2349817
>     end
> where primary_key in (101,102,103)

> From the inserted and deleted tables, it is impossible to know how to
update
> a child table with a foreign key to ParentTable.primary_key.  You know the
> rows of the child table linking to 101, 102, or 103 must be updated, and
you
> know that the new parent_key values must be 1883726, 498378, and 2349817,
> since you see those values in the inserted table, but how do you know
> (without seeing the query!) what the new parent_key values should be?  I
> don't see how this information can be discovered from the inserted and
> deleted tables unless there is another candidate key that is not altered.

> SK

> > there is no problem to update.

> > inserted table and deleted table will have same rows when update trigger
> > fires. deleted table will contain old values and inserted table will
have
> > new values. now, you may able to update the child table. it's easy to
make
> > relation between them if you know, how to relate two tables in the
> > sqlserver. there is no special treatment need for that.

> > Regards,

> > Rajesh Patel

> > > Here is my situation:

> > > I have a "master" table that has the following PK

> > > Visit    decimal(5,2)
> > > Test    integer

> > > I have a "detail" table with this PK

> > > Subject    integer
> > > Visit        decimal(5,2)
> > > Test        integer

> > > I cannot have enforced referential integrity between these tables, but
I
> > > need to be able to cascade the update of "Test" from the master to
> certain
> > > records in the detail table.
> > > This presents no problem in a single-record update in the master.  But
> > when
> > > I have a multiple record update, because I have altered the primary
key
> of
> > > master, I can't figure out how (or whether) I can relate records in
the
> > > deleted table to the inserted table.  BOL says that the records in
> deleted
> > > won't normally have matching records in the trigger table, and that
> > inserted
> > > is a copy of the new records in the trigger table.  Unless there is a
> > > "bookmark" or recnum that can help me relate deleted to inserted, I'm
> > > looking at coding the two-step process of 1) killing the detail
records
> > that
> > > match the key in deleted and 2) inserting the records from inserted
that
> > > aren't found in detail.  Can anyone confirm that this is my only
option
> > (for
> > > a multi-row update where the PK changes)?

> > > I'm trying to get more info on the IF UPDATE(column) to see if
anything
> > can
> > > be done here.  I'm assuming at this point that the UPDATE(column) will
> be
> > > true for every record in deleted, even if the value was not really
> > changed.
> > > For example, "UPDATE Addresses SET State = 'OH'" would update every
> record
> > > in Addresses, even those whose State was already "OH".  So IF
> > UPDATE(column)
> > > is not something I can test on a row-by-row basis, to see if the value
> in
> > > the column was truly changed.  Again, can anyone confirm?

> > > TIA,

> > > Joe

>   Am I missing something, or can you do this simply by defining the
foreign
> key relationship with ON UPDATE CASCADE ?  If not, can you explain what
you
> are trying to do that won't be done by the cascading constraint?

> SK

> > Here is my situation:

> > I have a "master" table that has the following PK

> > Visit    decimal(5,2)
> > Test    integer

> > I have a "detail" table with this PK

> > Subject    integer
> > Visit        decimal(5,2)
> > Test        integer

> > I cannot have enforced referential integrity between these tables, but I
> > need to be able to cascade the update of "Test" from the master to
certain
> > records in the detail table.
> > This presents no problem in a single-record update in the master.  But
> when
> > I have a multiple record update, because I have altered the primary key
of
> > master, I can't figure out how (or whether) I can relate records in the
> > deleted table to the inserted table.  BOL says that the records in
deleted
> > won't normally have matching records in the trigger table, and that
> inserted
> > is a copy of the new records in the trigger table.  Unless there is a
> > "bookmark" or recnum that can help me relate deleted to inserted, I'm
> > looking at coding the two-step process of 1) killing the detail records
> that
> > match the key in deleted and 2) inserting the records from inserted that
> > aren't found in detail.  Can anyone confirm that this is my only option
> (for
> > a multi-row update where the PK changes)?

> > I'm trying to get more info on the IF UPDATE(column) to see if anything
> can
> > be done here.  I'm assuming at this point that the UPDATE(column) will
be
> > true for every record in deleted, even if the value was not really
> changed.
> > For example, "UPDATE Addresses SET State = 'OH'" would update every
record
> > in Addresses, even those whose State was already "OH".  So IF
> UPDATE(column)
> > is not something I can test on a row-by-row basis, to see if the value
in
> > the column was truly changed.  Again, can anyone confirm?

> > TIA,

> > Joe

> CREATE TABLE Parent
> (
>  COL INT PRIMARY KEY
> )

> INSERT INTO Parent VALUES (1)
> INSERT INTO Parent VALUES (2)
> INSERT INTO Parent VALUES (3)

> CREATE TABLE Child
> (
>  COL1 INT PRIMARY KEY,
>  COL2 INT
> )
> INSERT INTO Child VALUES (1,1)
> INSERT INTO Child VALUES (2,1)
> INSERT INTO Child VALUES (3,2)
> INSERT INTO Child VALUES (4,2)
> INSERT INTO Child VALUES (5,2)
> INSERT INTO Child VALUES (6,3)

> SELECT * FROM Parent
> SELECT * FROM Child
> GO
> CREATE TRIGGER MY_TR ON Parent FOR UPDATE
> AS
> UPDATE Child SET COL2=I.COL FROM INSERTED I JOIN
> Parent P ON I.COL=P.COL
> WHERE  COL2 IN (SELECT COL FROM DELETED)
> GO

> UPDATE Parent SET COL=
>  case when COL = 1 then 1883726
>          when COL = 2 then 498378

>     end
> where COL in (1,2)

> DROP TABLE Child
> DROP TABLE Parent

> > Rajesh,

> >   If the column being changed is the one the child table refers to, I
> don't
> > think you are correct.

> > Suppose this is the update:

> > update ParentTable set
> >   primary_key =
> >     case when primary_key = 101 then 1883726
> >          when primary_key = 102 then 498378
> >          when primary_key = 103 then 2349817
> >     end
> > where primary_key in (101,102,103)

> > From the inserted and deleted tables, it is impossible to know how to
> update
> > a child table with a foreign key to ParentTable.primary_key.  You know
the
> > rows of the child table linking to 101, 102, or 103 must be updated, and
> you
> > know that the new parent_key values must be 1883726, 498378, and
2349817,
> > since you see those values in the inserted table, but how do you know
> > (without seeing the query!) what the new parent_key values should be?  I
> > don't see how this information can be discovered from the inserted and
> > deleted tables unless there is another candidate key that is not
altered.

> > SK

> > > there is no problem to update.

> > > inserted table and deleted table will have same rows when update
trigger
> > > fires. deleted table will contain old values and inserted table will
> have
> > > new values. now, you may able to update the child table. it's easy to
> make
> > > relation between them if you know, how to relate two tables in the
> > > sqlserver. there is no special treatment need for that.

> > > Regards,

> > > Rajesh Patel

> > > > Here is my situation:

> > > > I have a "master" table that has the following PK

> > > > Visit    decimal(5,2)
> > > > Test    integer

> > > > I have a "detail" table with this PK

> > > > Subject    integer
> > > > Visit        decimal(5,2)
> > > > Test        integer

> > > > I cannot have enforced referential integrity between these tables,
but
> I
> > > > need to be able to cascade the update of "Test" from the master to
> > certain
> > > > records in the detail table.
> > > > This presents no problem in a single-record update in the master.
But
> > > when
> > > > I have a multiple record update, because I have altered the primary
> key
> > of
> > > > master, I can't figure out how (or whether) I can relate records in
> the
> > > > deleted table to the inserted table.  BOL says that the records in
> > deleted
> > > > won't normally have matching records in the trigger table, and that
> > > inserted
> > > > is a copy of the new records in the trigger table.  Unless there is
a
> > > > "bookmark" or recnum that can help me relate deleted to inserted,
I'm
> > > > looking at coding the two-step process of 1) killing the detail
> records
> > > that
> > > > match the key in deleted and 2) inserting the records from inserted
> that
> > > > aren't found in detail.  Can anyone confirm that this is my only
> option
> > > (for
> > > > a multi-row update where the PK changes)?

> > > > I'm trying to get more info on the IF UPDATE(column) to see if
> anything
> > > can
> > > > be done here.  I'm assuming at this point that the UPDATE(column)
will
> > be
> > > > true for every record in deleted, even if the value was not really
> > > changed.
> > > > For example, "UPDATE Addresses SET State = 'OH'" would update every
> > record
> > > > in Addresses, even those whose State was already "OH".  So IF
> > > UPDATE(column)
> > > > is not something I can test on a row-by-row basis, to see if the
value
> > in
> > > > the column was truly changed.  Again, can anyone confirm?

> > > > TIA,

> > > > Joe

> Thanks for the replies.  My internet connection went down 5 minutes after
I
> submitted this post, and I just got back online.

> The reason I can't use cascading update is because I cannot enforce
> referential integrity.  The child table can have values in the Visit
column
> that the parent doesn't have.  For instance, master may have visits
1,2,3,4;
> child would also have 1,2,3,4 but may also have 1.5, 2.1, etc.  These 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 ability to
> cascade the update.

> Joe


> > Joe,

> >   Am I missing something, or can you do this simply by defining the
> foreign
> > key relationship with ON UPDATE CASCADE ?  If not, can you explain what
> you
> > are trying to do that won't be done by the cascading constraint?

> > SK

> > > Here is my situation:

> > > I have a "master" table that has the following PK

> > > Visit    decimal(5,2)
> > > Test    integer

> > > I have a "detail" table with this PK

> > > Subject    integer
> > > Visit        decimal(5,2)
> > > Test        integer

> > > I cannot have enforced referential integrity between these tables, but
I
> > > need to be able to cascade the update of "Test" from the master to
> certain
> > > records in the detail table.
> > > This presents no problem in a single-record update in the master.  But
> > when
> > > I have a multiple record update, because I have altered the primary
key
> of
> > > master, I can't figure out how (or whether) I can relate records in
the
> > > deleted table to the inserted table.  BOL says that the records in
> deleted
> > > won't normally have matching records in the trigger table, and that
> > inserted
> > > is a copy of the new records in the trigger table.  Unless there is a
> > > "bookmark" or recnum that can help me relate deleted to inserted, I'm
> > > looking at coding the two-step process of 1) killing the detail
records
> > that
> > > match the key in deleted and 2) inserting the records from inserted
that
> > > aren't found in detail.  Can anyone confirm that this is my only
option
> > (for
> > > a multi-row update where the PK changes)?

> > > I'm trying to get more info on the IF UPDATE(column) to see if
anything
> > can
> > > be done here.  I'm assuming at this point that the UPDATE(column) will
> be
> > > true for every record in deleted, even if the value was not really
> > changed.
> > > For example, "UPDATE Addresses SET State = 'OH'" would update every
> record
> > > in Addresses, even those whose State was already "OH".  So IF
> > UPDATE(column)
> > > is not something I can test on a row-by-row basis, to see if the value
> in
> > > the column was truly changed.  Again, can anyone confirm?

> > > TIA,

> > > 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 no way
> to know how to update the child table.

>   To be honest, I think you should redesign your database.  From what
little
> you've said, you can enter any value into child.visit.  If that value
> 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 no
> 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 where
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 fractional
> 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 in
the
> same table as the other rows, would then have a NULL in the foreign key
> column if it is not related to a parent record.

> SK

> > To all,

> > Thanks for the replies.  My internet connection went down 5 minutes
after
> I
> > submitted this post, and I just got back online.

> > The reason I can't use cascading update is because I cannot enforce
> > referential integrity.  The child table can have values in the Visit
> column
> > that the parent doesn't have.  For instance, master may have visits
> 1,2,3,4;
> > child would also have 1,2,3,4 but may also have 1.5, 2.1, etc.  These
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 ability to
> > cascade the update.

> > Joe


> > > Joe,

> > >   Am I missing something, or can you do this simply by defining the
> > foreign
> > > key relationship with ON UPDATE CASCADE ?  If not, can you explain
what
> > you
> > > are trying to do that won't be done by the cascading constraint?

> > > SK

> > > > Here is my situation:

> > > > I have a "master" table that has the following PK

> > > > Visit    decimal(5,2)
> > > > Test    integer

> > > > I have a "detail" table with this PK

> > > > Subject    integer
> > > > Visit        decimal(5,2)
> > > > Test        integer

> > > > I cannot have enforced referential integrity between these tables,
but
> I
> > > > need to be able to cascade the update of "Test" from the master to
> > certain
> > > > records in the detail table.
> > > > This presents no problem in a single-record update in the master.
But
> > > when
> > > > I have a multiple record update, because I have altered the primary
> key
> > of
> > > > master, I can't figure out how (or whether) I can relate records in
> the
> > > > deleted table to the inserted table.  BOL says that the records in
> > deleted
> > > > won't normally have matching records in the trigger table, and that
> > > inserted
> > > > is a copy of the new records in the trigger table.  Unless there is
a
> > > > "bookmark" or recnum that can help me relate deleted to inserted,
I'm
> > > > looking at coding the two-step process of 1) killing the detail
> records
> > > that
> > > > match the key in deleted and 2) inserting the records from inserted
> that
> > > > aren't found in detail.  Can anyone confirm that this is my only
> option
> > > (for
> > > > a multi-row update where the PK changes)?

> > > > I'm trying to get more info on the IF UPDATE(column) to see if
> anything
> > > can
> > > > be done here.  I'm assuming at this point that the UPDATE(column)
will
> > be
> > > > true for every record in deleted, even if the value was not really
> > > changed.
> > > > For example, "UPDATE Addresses SET State = 'OH'" would update every
> > record
> > > > in Addresses, even those whose State was already "OH".  So IF
> > > UPDATE(column)
> > > > is not something I can test on a row-by-row basis, to see if the
value
> > in
> > > > the column was truly changed.  Again, can anyone confirm?

> > > > TIA,

> > > > Joe

> 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.

> 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 no
way
> > to know how to update the child table.

> >   To be honest, I think you should redesign your database.  From what
> little
> > you've said, you can enter any value into child.visit.  If that value
> > 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 no
> > 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 where
> 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 fractional
> > 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 in
> the
> > same table as the other rows, would then have a NULL in the foreign key
> > 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 minutes
> after
> > I
> > > submitted this post, and I just got back online.

> > > The reason I can't use cascading update is because I cannot enforce
> > > referential integrity.  The child table can have values in the Visit
> > column
> > > that the parent doesn't have.  For instance, master may have visits
> > 1,2,3,4;
> > > child would also have 1,2,3,4 but may also have 1.5, 2.1, etc.  These
> 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 ability
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 the
> > > foreign
> > > > key relationship with ON UPDATE CASCADE ?  If not, can you explain
> 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 have a "master" table that has the following PK

> > > > > Visit    decimal(5,2)
> > > > > Test    integer

> > > > > I have a "detail" table with this PK

> > > > > Subject    integer
> > > > > Visit        decimal(5,2)
> > > > > Test        integer

> > > > > I cannot have enforced referential integrity between these tables,
> but
> > I
> > > > > need to be able to cascade the update of "Test" from the master to
> > > certain
> > > > > records in the detail table.
> > > > > This presents no problem in a single-record update in the master.
> But
> > > > when
> > > > > I have a multiple record update, because I have altered the
primary
> > key
> > > of
> > > > > master, I can't figure out how (or whether) I can relate records
in
> > the
> > > > > deleted table to the inserted table.  BOL says that the records in
> > > deleted
> > > > > won't normally have matching records in the trigger table, and
that
> > > > inserted
> > > > > is a copy of the new records in the trigger table.  Unless there
is
> a
> > > > > "bookmark" or recnum that can help me relate deleted to inserted,
> I'm
> > > > > looking at coding the two-step process of 1) killing the detail
> > records
> > > > that
> > > > > match the key in deleted and 2) inserting the records from
inserted
> > that
> > > > > aren't found in detail.  Can anyone confirm that this is my only
> > option
> > > > (for
> > > > > a multi-row update where the PK changes)?

> > > > > I'm trying to get more info on the IF UPDATE(column) to see if
> > anything
> > > > can
> > > > > be done here.  I'm assuming at this point that the UPDATE(column)
> will
> > > be
> > > > > true for every record in deleted, even if the value was not really
> > > > changed.
> > > > > For example, "UPDATE Addresses SET State = 'OH'" would update
every
> > > record
> > > > > in Addresses, even those whose State was already "OH".  So IF
> > > > UPDATE(column)
> > > > > is not something I can test on a row-by-row basis, to see if the
> value
> > > in
> > > > > the column was truly changed.  Again, can anyone confirm?

> > > > > TIA,

> > > > > Joe

>   I still don't understand.  Here are things I think will not work, based
on
> 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
the
> parent table?
> 2.  update of two master primary keys 101 -> 202 and 102 -> 302.  If child
> 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
101's
> become 302 and the 102's become 202 or vice versa?  You can tell this from
> 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
not.
> 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
(especially
> your comments like "any number within the protocol - is "within the
> protocol" anything maintained in tables?), so perhaps if you could post
the
> table structures and sample data that includes all the variations along
with
> 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 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.

> > 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 no
> way
> > > to know how to update the child table.

> > >   To be honest, I think you should redesign your database.  From what
> > little
> > > you've said, you can enter any value into child.visit.  If that value
> > > 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
no
> > > 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
where
> > 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
fractional
> > > 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
in
> > the
> > > same table as the other rows, would then have a NULL in the foreign
key
> > > 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 minutes
> > after
> > > I
> > > > submitted this post, and I just got back online.

> > > > The reason I can't use cascading update is because I cannot enforce
> > > > referential integrity.  The child table can have values in the Visit
> > > column
> > > > that the parent doesn't have.  For instance, master may have visits
> > > 1,2,3,4;
> > > > child would also have 1,2,3,4 but may also have 1.5, 2.1, etc.
These
> > 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 ability
> 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
the
> > > > foreign
> > > > > key relationship with ON UPDATE CASCADE ?  If not, can you explain
> > 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 have a "master" table that has the following PK

> > > > > > Visit    decimal(5,2)
> > > > > > Test    integer

> > > > > > I have a "detail" table with this PK

> > > > > > Subject    integer
> > > > > > Visit        decimal(5,2)
> > > > > > Test        integer

> > > > > > I cannot have enforced referential integrity between these
tables,
> > but
> > > I
> > > > > > need to be able to cascade the update of "Test" from the master
to
> > > > certain
> > > > > > records in the detail table.
> > > > > > This presents no problem in a single-record update in the
master.
> > But
> > > > > when
> > > > > > I have a multiple record update, because I have altered the
> primary
> > > key
> > > > of
> > > > > > master, I can't figure out how (or whether) I can relate records
> in
> > > the
> > > > > > deleted table to the inserted table.  BOL says that the records
in
> > > > deleted
> > > > > > won't normally have matching records in the trigger table, and
> that
> > > > > inserted
> > > > > > is a copy of the new records in the trigger table.  Unless there
> is
> > a
> > > > > > "bookmark" or recnum that can help me relate deleted to
inserted,
> > I'm
> > > > > > looking at coding the two-step process of 1) killing the detail
> > > records
> > > > > that
> > > > > > match the key in deleted and 2) inserting the records from
> inserted
> > > that
> > > > > > aren't found in detail.  Can anyone confirm that this is my only
> > > option
> > > > > (for
> > > > > > a multi-row update where the PK changes)?

> 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
table,
> 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
visit,
> e.g. if between 2 and 3, then offer 2.1.  The user can accept this or
choose
> 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
point
> 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
protocol
> 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
9
> 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.

> Joe

> "Steve Kass" <sk...@drew.edu> wrote in message
> news:OjSJ0mc5DHA.2416@TK2MSFTNGP10.phx.gbl...
> > Joe,

> >   I still don't understand.  Here are things I think will not work,
based
> on
> > 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
> the
> > parent table?
> > 2.  update of two master primary keys 101 -> 202 and 102 -> 302.  If
child
> > 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
> 101's
> > become 302 and the 102's become 202 or vice versa?  You can tell this
from
> > 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
> not.
> > 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
> (especially
> > your comments like "any number within the protocol - is "within the
> > protocol" anything maintained in tables?), so perhaps if you could post
> the
> > table structures and sample data that includes all the variations along
> with
> > 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
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.

> > > 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
no
> > way
> > > > to know how to update the child table.

> > > >   To be honest, I think you should redesign your database.  From
what
> > > little
> > > > you've said, you can enter any value into child.visit.  If that
value
> > > > 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
> no
> > > > 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
> where
> > > 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
> fractional
> > > > 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
> in
> > > the
> > > > same table as the other rows, would then have a NULL in the foreign
> key
> > > > 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
minutes
> > > after
> > > > I
> > > > > submitted this post, and I just got back online.

> > > > > The reason I can't use cascading update is because I cannot
enforce
> > > > > referential integrity.  The child table can have values in the
Visit
> > > > column
> > > > > that the parent doesn't have.  For instance, master may have
visits
> > > > 1,2,3,4;
> > > > > child would also have 1,2,3,4 but may also have 1.5, 2.1, etc.
> These
> > > 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
ability
> > 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
> the
> > > > > foreign
> > > > > > key relationship with ON UPDATE CASCADE ?  If not, can you
explain
> > > 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