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 10: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 Pat » Thu, 29 Jan 2004 12: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






when
inserted
that
(for
can
changed.
UPDATE(column)

 
 
 

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

Post by Steve Kas » Thu, 29 Jan 2004 12: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



when
inserted
that
(for
can
changed.
UPDATE(column)
 
 
 

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

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

ajesh,

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


"Rajesh Patel" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
certain
of
deleted
be
record
in


 
 
 

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

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

teve
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

"Steve Kass" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
don't
update
you
have
make
I
key
the
records
that
option
anything


 
 
 

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

Post by Joe Clark » Fri, 30 Jan 2004 00: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


foreign
you


certain
of
deleted
be
record
in
 
 
 

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

Post by Steve Kas » Fri, 30 Jan 2004 01:25:03

ri,

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

"Uri Dimant" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...
.
the
2349817,
altered.
trigger
but
But
a
I'm
will
value


 
 
 

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

Post by Steve Kas » Fri, 30 Jan 2004 01:40:43

oe,

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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
I
column
1,2,3,4;
I
key
the
records
that
option
anything


 
 
 

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

Post by Joe Clark » Fri, 30 Jan 2004 02:29:27

teve,

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" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...
little
if
it
decimal
in
the
after
are
as
what
but
But
a
I'm
will
value


 
 
 

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

Post by Steve Kas » Fri, 30 Jan 2004 03:07:54

oe,

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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
that
mind,
course
If
then
a
prevent
visits
that
said,
eg.
multi-row
way
column
table
to
primary
in
that
is
inserted
every


 
 
 

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

Post by Anith Se » Fri, 30 Jan 2004 03: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 » Fri, 30 Jan 2004 05:29:47

. 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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
on
the
101's
not.
(especially
the
with
take
table,
creating
trigger.
"Change
you
integer
no
sort -
where
fractional
in
key
These
the
tables,
to
master.
in
inserted,
UPDATE(column)
really


 
 
 

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

Post by Steve Kas » Fri, 30 Jan 2004 05:48:59

oe,

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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
If
status
table,
visit,
choose
point
protocol
9
based
child
from
anticipate
database.
master
protocol
master
update,
no
what
value
minutes
enforce
Visit
visits
ability
explain
master
records
records
there
detail
only
the


 
 
 

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

Post by Joe Clark » Fri, 30 Jan 2004 06: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



may
transaction.
unreliable
 
 
 

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

Post by v-baiwe » Sat, 31 Jan 2004 16: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.