puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Joe Clark » Thu, 29 Jan 2004 03:26:51



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

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Rajesh Pate » Thu, 29 Jan 2004 05:14:06


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


Quote:> 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


 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Steve Kas » Thu, 29 Jan 2004 05:18:44


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


Quote:> 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

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Steve Kas » Thu, 29 Jan 2004 05:40:30


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

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Uri Diman » Thu, 29 Jan 2004 08:43:24


Steve
You are absolutely right that in this case we should use ON UPDATE CASCADE .
I've just tested the sitautuion that you are talking about ( I hope i
understood it properly so if i don't please correct me ). Look at this one
and say what do you think

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

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Joe Clark » Thu, 29 Jan 2004 17:57:47


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

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Steve Kas » Thu, 29 Jan 2004 18:25:03


Uri,

  I'm not sure what the question is.  This trigger doesn't do anything
useful, and its results are not well-defined, since the update-from syntax
defines more than one update operation for each row of the target of the
update.

SK


> Steve
> You are absolutely right that in this case we should use ON UPDATE CASCADE
.
> I've just tested the sitautuion that you are talking about ( I hope i
> understood it properly so if i don't please correct me ). Look at this one
> and say what do you think

> 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

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Steve Kas » Thu, 29 Jan 2004 18:40:43


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

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Joe Clark » Thu, 29 Jan 2004 19:29:27


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


> 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

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Steve Kas » Thu, 29 Jan 2004 20:07:54


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)?

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

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Anith Se » Thu, 29 Jan 2004 20:22:20


Joe,

Adding to Steve's post, a couple of points...

First, it is neither practical nor sensible to assume data manipulation
operations in a relational database will affect only a single row in a
table. By somehow preventing the users from updating multiple rows, you may
be robbing them of some inherent advantages of the DBMS's set based
manipulative capabilities.

Second, lack of sufficient integrity is a database design problem. In your
case, lack of referential integrity is a serious issue, which you seem to
trivialize and it is exactly the problem you are facing now.

Since you are set to enforce the referential integrity from your
application, one approach you may try is to issue two insert statements --
one for the parent & the other for the child -- within a single transaction.
And make sure all your users & applications follow the same approach
everywhere else. It is quite obvious that it is a ridiculous and unreliable
approach, but it is the price you pay for ignoring data integrity in the
first place.

--
Anith

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Joe Clark » Thu, 29 Jan 2004 22:29:47


1.  No, I do not have to change the 2345.  If it is already there, fine.  If
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 status
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 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 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)?

...

read more »

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Steve Kas » Thu, 29 Jan 2004 22:48:59


Joe,

  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?

  What you say about #1 is particularly confusing - if a data entry person
decides that 2345 is a suitable new value for a primary key, and 2345 exists
in the child table to represent records not associated with any parent
record, then you don't mind if these child records are suddenly associated
with a parent record they should have nothing to do with?

  I don't know what Access was able to do that SQL Server can't, and I can
try to help if you provide the relevant DDL and an example of an Access
update and the changes it makes that you can't do in SQL Server.

SK

"Joe Clarke" <j...@integra-sys.com> wrote in message

news:uG0YV1d5DHA.1504@TK2MSFTNGP12.phx.gbl...
> 1.  No, I do not have to change the 2345.  If it is already there, fine.
If
> 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
status
> 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
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

...

read more »

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Joe Clark » Thu, 29 Jan 2004 23:21:43


Anith,

I'll just assume your first point is for Steve.

As to the second point, the database is not lacking in referential
integrity, with cascading updates and deletes where appropriate.  I have
about 95 tables with a max cardinality of 12, so there's relationships up
the "wazoo".  But I can't enforce referential integrity between these two
tables.  The "master" table does not own or control the "child" - it merely
advises.  The relationship between these tables is one of equivolence or
resemblance.

The only referential integrity enforced from application side is to prevent
the deletion of records in "child" that have matches in "master".  This can
be done via a trigger in SQL Server.  Likewise, the insert to "child" can be
placed in an insert trigger for "master", though that doesn't border on
referential integrity.

Joe


Quote:> Joe,

> Adding to Steve's post, a couple of points...

> First, it is neither practical nor sensible to assume data manipulation
> operations in a relational database will affect only a single row in a
> table. By somehow preventing the users from updating multiple rows, you
may
> be robbing them of some inherent advantages of the DBMS's set based
> manipulative capabilities.

> Second, lack of sufficient integrity is a database design problem. In your
> case, lack of referential integrity is a serious issue, which you seem to
> trivialize and it is exactly the problem you are facing now.

> Since you are set to enforce the referential integrity from your
> application, one approach you may try is to issue two insert statements --
> one for the parent & the other for the child -- within a single
transaction.
> And make sure all your users & applications follow the same approach
> everywhere else. It is quite obvious that it is a ridiculous and
unreliable
> approach, but it is the price you pay for ignoring data integrity in the
> first place.

> --
> Anith

 
 
 

puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

Post by Baisong Wei[MSF » Sat, 31 Jan 2004 09:59:41


Hi Joe,

Thank you for using the newsgroup and it my pleasure to help you with you
issue.

Actually,  the integrity  is always a key factor that we should work on in
a database design. A trigger could work to operating on on two tables, but
it is not the way a relational database work.  Still, I suggest a database
re-design. On your table, if not re-design totally,  I think you could have
new table contained with the record that will have reference with Master
database. the other records will be in another table. Then you could create
a view to select  from two tables by union them together since they have
the same table schema.

Hope this helps and if you still have questions, please feel free to post
new message here and I am ready to help!

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

 
 
 

1. Update Trigger referencing only updated columns in Deleted and Inserted

I am trying to use an update trigger to generate Log
information of updated columns on a user table. I now know
how to identify which columns that has been changed but I
can't figure out how to reference these columns (and only
these columns) in table 'Inserted' and/or table 'Deleted'.
My objective is to create two string variables that are to
hold old values and New values:

OldValues=
'UpdatedColumn1=OldValue1,UpdatedColumn2=OldValue2...'

NewValues=
'UpdatedColumn1=NewValue1,UpdatedColumn2=NewValue2...'

Please
Sten Bille

2. How do I get Developer6 to connect to Oracle 8.1.5i

3. Referencing only updated columns from inserted in an update trigger

4. Urgent - DB upgrade and OWS

5. Create Trigger - Insert, Update - not updating record on Insert

6. US-TN/AL/GA Lan Administrator (6 Openings) - Recruiter

7. Insert/Update Trigger performing Updates

8. Database format of the German phonebook from DeTeMedien

9. trigger - update a record on insert/update

10. Updating puzzle - Update without interfering On-Line users