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

Mulitple Create Views in Query Batch

I have a script that I am running from a Query Analyser that I want to put
into a SP eventually.
In the script, I have 5 Create Views (which I drop when the script exits).
But I have to have "GO" after each Create View or I will get an error:
'CREATE VIEW' must be the first statement in a query batch.'
The problem is I also have a beginning date and ending date that I am using
in my query after the Views are created. But I can't declare and set them
up until after the Create Views are done.
Create View...
go
Create View...
go
Create View
go
declare @.StartDate smallDateTime,@.EndDate smallDateTime
select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
select ...
drop View...
What I would like to do is put the declares at the top of the script so that
it will be easier to find for the person running the script to allow them to
change dates as they will be running this 6 or 7 times for different date
ranges.
This is just a one time project, so I don't want to set up a SP at the
moment or write a simple GUI to handle it.
Is there a way to do this (put the dates at the top somehow)?
Also, is the multiple creation of Views a problem in a SP also?
Thanks,
TomTshad,
I've never done what you're asking and I'm not totally sure why you would.
That being said, your variable is batch specific and cannot span batches.
Also, I'm not sure how you would code multiple GOs in your stored procedure.
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> I've never done what you're asking and I'm not totally sure why you would.
> That being said, your variable is batch specific and cannot span batches.
> Also, I'm not sure how you would code multiple GOs in your stored
> procedure.
But if you can only create one View in a Batch, that would be a problem in a
SP where you may need to create more than one.
But I don't know how it is done either.
This is being done to do one specific script to move selected data into a
CSV file to move some data from a client site to ours. We are just going to
give them the script to run. They will run it from Query Analyser and they
can save the results to a tab delimited file and send it to us.
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>|||Tshad,
Your variables can be persisted across batches by using a temporary table or
a permanent table - table can be dropped at the end of the script. Why does
this need to be embedded in a proc? Can you just give them a .sql script to
run?
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> But if you can only create one View in a Batch, that would be a problem in
> a SP where you may need to create more than one.
> But I don't know how it is done either.
> This is being done to do one specific script to move selected data into a
> CSV file to move some data from a client site to ours. We are just going
> to give them the script to run. They will run it from Query Analyser and
> they can save the results to a tab delimited file and send it to us.
> Tom
>|||Look up CREATE SCHEMA in BOL.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
That is what I am doing. The problem is that the Declares for the dates are
halfway down the script. And I was just trying to make it easy on them. It
isn't a big problem, just that I would be nice for them to be able to change
the dates at the top of the script.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server wants to see just one CREATE VIEW in a batch of its own.
That is just the rules. If you think about it, how could you use a
view that is created in the same batch as code that references it?
Only if this were procedural code that is executed, step by step, like
a 3GL, instead of an RDBMS.
But a better question is why would you create VIEWs and then drop them?
That is not what VIEWs are for. Use derived tables, CTE, etc. if you
want to to do this kind of thing. I'll bet you are still thinking in
3GL terms and want to fake a bunch of scratch files.|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
It doesn't. And I did give them an sql script. I was trying to see if
there was a way to move the variable declares to the top of files in the
first batch. It wasn't necessary, just curious if there was a way to do it.
As far as the procedure, I was just asking as I may want to set up an SP
using multiple views and if you can't do it as a batch, I may not be able to
do it in a procedure, either.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127446041.456812.95650@.g47g2000cwa.googlegroups.com...
> SQL Server wants to see just one CREATE VIEW in a batch of its own.
> That is just the rules. If you think about it, how could you use a
> view that is created in the same batch as code that references it?
> Only if this were procedural code that is executed, step by step, like
> a 3GL, instead of an RDBMS.
Why not?
I am creating multiple Views that I am using temporarily and I am not sure
why it is obvious that you can't create a View and then immediately
reference it. There probably is a good reason for it, but I don't know what
or why it is.

> But a better question is why would you create VIEWs and then drop them?
> That is not what VIEWs are for. Use derived tables, CTE, etc. if you
> want to to do this kind of thing. I'll bet you are still thinking in
> 3GL terms and want to fake a bunch of scratch files.
You are right. That is the way I think. So shoot me. :)
This may not be what Views are for, but they solve a huge problem for me and
worked great.
May not have been the cleanest way, but I liked it. As a matter of fact, I
did use one table that I selected into and 5 Views, which worked great (took
a little time to put together with a great deal of help from Hugo).
As far as derived tables, I used those also. But for my select statement
(which was very large - at least I thought so) I was doing quite a bit of
work to get all my data to go across one line for each record ( I know there
are no fields, records or tables). This was for a csv file import/export.
According to what Hugo explained I was, in effect, using derived tables in
the form of Views. But if I had to replace all my references to my views
with derived tables, I think it would have been a bear to debug and my files
would have been 10-20 times larger.
I look at this as using the Views as a subroutine or macro that I call
instead of placing multiple instances of the same code throughout my select
statement. Much cleaner, even if not as efficient.
Tom|||>> Why not? <<
VIEWs can be built on VIEWs; they have to be created in order. Think
about it
NO, NO, NO!! You create VIEWs because they have meaning as data
elements that "persist" over many queries. There are no "temporary
kind of things" in a good data model. Damn it, man, you are still
writing scratch files in a 1960's COBOL system!
Not a problem, but if you do not learn, we will have to kill you\
. Hey, if I could not get at least a breakdown or a suicide during
final exams, the quarter was a waste!
VIEWs are good, but they are part of a schema design and need to be
planned as much as any other tabel. In spite of the myth, size does
not matter. In RDBMS, unlike sex, speed is better [note to self: I am
going har this quote again]

MS-SQL Script Question

MS-SQL 2000

Is there any way to run a SQL script against MSDE other than with OSQL? (No Enterprise manager or Query Analyzer)

TIA

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.comYou will have to have a client app of some sort (osql/isql/etc). Basically, the
client app will open a connection to your server, parse the content of your
script file into batches delimited by GO, then execute the batches against the
server.

It's quite easy to implement an ado connection to sqlserver, parse the script
file and execute it. QALite on the site does just that.

--
-oj
http://www.rac4sql.net

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:LnnKb.753782$Tr4.2103435@.attbi_s03...
MS-SQL 2000

Is there any way to run a SQL script against MSDE other than with OSQL? (No
Enterprise manager or Query Analyzer)

TIA

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related
website.
http://www.vehiclewebstudio.com|||The script can be registered as a task and run automatically
without operator intervention.

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:LnnKb.753782$Tr4.2103435@.attbi_s03...
MS-SQL 2000

Is there any way to run a SQL script against MSDE other than with OSQL? (No
Enterprise manager or Query Analyzer)

TIA

--
Tim Morrison

-----------------------
--

Vehicle Web Studio - The easiest way to create and maintain your vehicle
related website.
http://www.vehiclewebstudio.com

MSSQL Script execute in .bat

Hi Guys, anyone out there who could help me with my problem?

I have created a .bat file that would execute the mssql script and give an .txt file as an output. The script run successfully except for the output, the output save as .txt file was not in order unlike the output that was derrived from the query analyzer. As I try to solve my problem I observe that the lines separating the header and the data was the cause of un-order format, the lines doubles its length when I use the .bat file to execute my script. I have tried using the substring syntax to limit the length but same results achieved. Could anyone help me to remove those line automatically when I run my .bat file?

Please see the outputs below. Thank you very much.

output from query analyzer

object_name counter_name
-------- -----------
SQLServer:Buffer Manager Buffer cache hit ratio
SQLServer:Buffer Manager Buffer cache hit ratio base

(2 row(s) affected)

ouput from .bat

object_name
counter_name
----------------
----------------------
SQLServer:Buffer Manager
Buffer cache hit ratio
SQLServer:Buffer Manager
Buffer cache hit ratio baseFor anyone who will encounter same problem as I did, the solution for my problem was on the .bat file and not in MSSQL. I added the -W in the command in my .bat file.