Multi row key updates?

I have been using MS Viso (the one that integrates with Visual Studio .NET)
to generate ddl script for my database. It uses the following update trigger
code to enforce the referential integrity, but will only execute the code fo
r
one row updates, it will throw an error for multi-row updates. I am wonderin
g
why it only allows the one row, as it appears (to me) that the code will wor
k
fine for multi-row updates as well.
UPDATE "ReferedTable"
SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
FROM inserted, deleted, "ReferedTable"
WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
I understand that it is updating the refered key column in the related
tables whenever the primary key column of the parent table is changed, but I
do not understand why it only allows one row at a time to be updated.
Can someone please explain this for me? And can it actually be used for
multi-row updates? If not then what would be a good way of doing it? Thanks--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Why not dispense w/ the trigger and use the ON UPDATE CASCADE and ON
DELETE CASCADE methods of a Foreign Key? E.g.:
create table t (
a char(1) primary key,
c char(2) not null
)
create table s (
a char(1) not null references t (a)
on update cascade on delete cascade,
d datetime not null
)
Whenever t.a is changed s.a will reflect the changes and all rows in s.a
will be updated.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQj4zo4echKqOuFEgEQKRgACg3GMmYjF9+Igx
UGwWwRMF3YJL5msAn0Le
4XoP+70vYIkNaJh/nfxGf6Nj
=Wx8M
--END PGP SIGNATURE--
Gary K wrote:
> I have been using MS Viso (the one that integrates with Visual Studio .NET
)
> to generate ddl script for my database. It uses the following update trigg
er
> code to enforce the referential integrity, but will only execute the code
for
> one row updates, it will throw an error for multi-row updates. I am wonder
ing
> why it only allows the one row, as it appears (to me) that the code will w
ork
> fine for multi-row updates as well.
> UPDATE "ReferedTable"
> SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
> FROM inserted, deleted, "ReferedTable"
> WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
> I understand that it is updating the refered key column in the related
> tables whenever the primary key column of the parent table is changed, but
I
> do not understand why it only allows one row at a time to be updated.
> Can someone please explain this for me? And can it actually be used for
> multi-row updates? If not then what would be a good way of doing it? Thanks[/color
]|||"MGFoster" wrote:

> Why not dispense w/ the trigger and use the ON UPDATE CASCADE and ON
> DELETE CASCADE methods of a Foreign Key? E.g.:
>
Mainly because SQL Server does not have ON UPDATE/DELETE RESTRICTED/SET
NULL/SET DEFAULT options. Also our database requirements specify before/afte
r
auditing which can only be done in INSTEAD OF triggers (due to the nature of
the tables used, which can't be changed, or at least not by me), and using
INSTEAD OF triggers precludes the use of UPDATE/DELETE foreign key
restrictions.
We have borrowed from the programming structure that Viso produces, in that
while we still create foreign key references they are disabled so we can
implement our own version of referential integrity + auditing.
Personally I would prefer to use another DB package, but unfortunately to
keep things cheap and easily integratable with our MS Office products we are
stuck with SQL Server.
Thanks for the reply MG, but sorry, it's not something we can use.|||Gary K wrote:
> "MGFoster" wrote:
>
> Mainly because SQL Server does not have ON UPDATE/DELETE RESTRICTED/SET
> NULL/SET DEFAULT options. Also our database requirements specify before/af
ter
> auditing which can only be done in INSTEAD OF triggers (due to the nature
of
> the tables used, which can't be changed, or at least not by me), and using
> INSTEAD OF triggers precludes the use of UPDATE/DELETE foreign key
> restrictions.
> We have borrowed from the programming structure that Viso produces, in tha
t
> while we still create foreign key references they are disabled so we can
> implement our own version of referential integrity + auditing.
> Personally I would prefer to use another DB package, but unfortunately to
> keep things cheap and easily integratable with our MS Office products we a
re
> stuck with SQL Server.
> Thanks for the reply MG, but sorry, it's not something we can use.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Is the Primary Key (PK) an Identity column? From BOL (Instead of Update
trigger):
"Usually, when an UPDATE statement that references a table attempts to
set the value of a computed, *identity*, or timestamp column, an error
is generated because the values for these columns must be determined by
Microsoft? SQL Server?. These columns must be included in the UPDATE
statement to meet the NOT NULL requirement of the column. However, if
the UPDATE statement references a view with an INSTEAD OF UPDATE
trigger, the logic defined in the trigger can bypass these columns and
avoid the error."
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQj5HZIechKqOuFEgEQJWDACePu5W/oh+PLuf3ysomu6DVtaT6IQAoLBB
y1D1g1dzEYdEoG6vh1+WNsx1
=IYDc
--END PGP SIGNATURE--|||"MGFoster" wrote:

> Is the Primary Key (PK) an Identity column? From BOL (Instead of Update
> trigger):
> "Usually, when an UPDATE statement that references a table attempts to
> set the value of a computed, *identity*, or timestamp column, an error
> is generated because the values for these columns must be determined by
> Microsoft? SQL Server?. These columns must be included in the UPDATE
> statement to meet the NOT NULL requirement of the column. However, if
> the UPDATE statement references a view with an INSTEAD OF UPDATE
> trigger, the logic defined in the trigger can bypass these columns and
> avoid the error."
>
Nope, I try to avoid those like the plague now. We use uniqueidentifiers as
ROWGUIDCOL columns, not only for 'bookmark' uses of such a column, but also
to make replication a lot easier.
The main point of my question was to confirm that the given code would
handle multi-row referential updates on a parent table primary key column
(which i have now done in a practical experiment), and to find out why the M
S
Viso designers would only let one row be updated at a time with the code.
I have confirmed that the code will perform multi-row updates and it appears
to work correctly, but as the famous quotes says, "Just because we can do
something, does it mean we SHOULD?" I am basically looking for any problems
that might arise from the use of the code.|||I should have included this in the last message, but here is the code I used
to test the multi-row update code.
use tempdb
go
-- these table testers are only so I can reuse the code if it needed any
changes (it did)
if objectproperty(object_id('tblb'), 'IsTable')=1
drop table tblb
go
if objectproperty(object_id('tbla'), 'IsTable')=1
drop table tbla
go
create table tbla ( -- our parent table in the relationship
-- (i couldn't be bothered typing in GUIDs so we use a tinyint pk)
ii tinyint NOT NULL PRIMARY KEY,
ll varchar(50) NULL
)
go
create table tblb ( -- our child table in the relationship
ii tinyint NOT NULL PRIMARY KEY,
ll varchar(50) NULL,
ia tinyint NOT NULL,
-- and this is our relationship contraint
CONSTRAINT FK_b FOREIGN KEY (ia) REFERENCES tbla (ii)
)
go
-- we are going to look after the integrity, so we disable the constraint
alter table tblb nocheck constraint FK_b
go
create trigger tbla_upd on tbla for update as
begin
-- our test trigger is only for the update condition, and since we control
what is
-- going to happen we can skip all the extra code we will be using.
-- this code will only be executed (for every child table) when the primary
key is updated
update tblb
set tblb.ia = inserted.ii
from inserted, deleted, tblb
where tblb.ia = deleted.ii
end
go
-- insert parent table values
insert tbla values (1, 'first')
insert tbla values (2, 'second')
insert tbla values (3, 'third')
-- insert child table values
insert tblb values (1, 'first/first', 1)
insert tblb values (2, 'first/second', 2)
insert tblb values (3, 'first/third', 3)
insert tblb values (4, 'second/third', 3)
insert tblb values (5, 'second/second', 2)
insert tblb values (6, 'third/second', 2)
insert tblb values (7, 'second/first', 1)
insert tblb values (8, 'third/third', 3)
go
-- what it looks like before we change things
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go
-- now we change the pk of the 'second' series to a new unique value
update tbla set ii=4 where ii=2
go
-- and we see what we get (works ok!)
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go
-- now we change the pk of the 'third' series to a value that is already in
use
-- (error testing, and yes it does throw an error as it is supposed to)
update tbla set ii=4 where ii=3
go
-- and then we see what we get after the change (which doesn't happen)
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go|||Gary,
The code looks wrong to me, but even so, it can't be
fixed unless there is another candidate key on the table.
First off, there is no join condition between inserted and
either of the other two tables. If 10 rows are updated, which
of the 10 inserted.PrimaryKey values will be assigned to
ReferedTable.ReferedKey? The way this proprietary
SQL Server syntax works, an arbitrary one of the 10
possibilities will be used.
But the problem is worse than that. Because the primary
key is being updated, there is no way to identify the correct
correspondence between an old row and a new row.
Suppose the update was this:
update T set
PrimaryKey =
case PrimaryKey
when 1 then 123
when 2 then 456
end
where PrimaryKey in (1,2)
Within the trigger, there is no way to distinguish
this update from a different one:
update T set
PrimaryKey =
case PrimaryKey
when 1 then 456
when 2 then 123
end
where PrimaryKey in (1,2)
At least not without it being possible to identify which
row is which on the basis of some column or columns
other than the PrimaryKey column.
If only one row is updated, the is no ambiguity.
Steve Kass
Drew University
Gary K wrote:

>I have been using MS Viso (the one that integrates with Visual Studio .NET)
>to generate ddl script for my database. It uses the following update trigge
r
>code to enforce the referential integrity, but will only execute the code f
or
>one row updates, it will throw an error for multi-row updates. I am wonderi
ng
>why it only allows the one row, as it appears (to me) that the code will wo
rk
>fine for multi-row updates as well.
>UPDATE "ReferedTable"
>SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
>FROM inserted, deleted, "ReferedTable"
>WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
>I understand that it is updating the refered key column in the related
>tables whenever the primary key column of the parent table is changed, but
I
>do not understand why it only allows one row at a time to be updated.
>Can someone please explain this for me? And can it actually be used for
>multi-row updates? If not then what would be a good way of doing it? Thanks
>|||Gary,
Check Itzik Ben-Gan's presentation & scripts on RI in SQL 7.0 and 2000 at
http://www.sql.co.il/ug/13/Thirteenth.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gary K" <GaryK@.discussions.microsoft.com> wrote in message
news:D3D0B4F4-EC82-41E6-A527-E6202A5394F7@.microsoft.com...
> I have been using MS Viso (the one that integrates with Visual Studio
.NET)
> to generate ddl script for my database. It uses the following update
trigger
> code to enforce the referential integrity, but will only execute the code
for
> one row updates, it will throw an error for multi-row updates. I am
wondering
> why it only allows the one row, as it appears (to me) that the code will
work
> fine for multi-row updates as well.
> UPDATE "ReferedTable"
> SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
> FROM inserted, deleted, "ReferedTable"
> WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
> I understand that it is updating the refered key column in the related
> tables whenever the primary key column of the parent table is changed, but
I
> do not understand why it only allows one row at a time to be updated.
> Can someone please explain this for me? And can it actually be used for
> multi-row updates? If not then what would be a good way of doing it?
Thanks