Re: Why data could not be committed into table?
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 11/01/04
- Next message: Hugo Kornelis: "Re: Multiple of 4? Better performance?"
- Previous message: Mal .mullerjannie_at_hotmail.com>: "RE: I'm not Trigger happy :-)"
- Next in thread: Jonathan Chong: "Re: Why data could not be committed into table?"
- Reply: Jonathan Chong: "Re: Why data could not be committed into table?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 01 Nov 2004 11:03:06 +0100
On Sat, 30 Oct 2004 05:49:56 +0800, Jonathan Chong wrote:
>Hope I don't miss out anything this time. If I do please let me know.
>Thanks.
Hi Jonathan,
Thanks for providing the code. Unfortunately, I was not able to reproduce
your problem. I ran your repro script to recreate the tables and fill them
with the one sample row you provided, then ran the following script in
Query Analyzer to mimick your console program:
declare @b dec(15,5), @a decimal(15,5), @m decimal(15,5)
select @b = 1.8, @a = 1.8001, @m = 1.8305
while @b < 1.8010
begin
update Feeds
set bid = @b, ask = @a, midPrice = @m where marketno = 1
select @b = @b + 0.0001, @a = @a + 0.0001, @m = @m + 0.0001
select * from markets
select * from marketdef
select * from Feeds
end
go
The data in markets and marketdef remained unchanged throughout the
execution (as I expected); the values of bid, ask and midPrice in table
Feeds each increased by 0.0001 per iteration until they were at 1.8009,
1.8010 and 1.83140. Could you try what happens if you run the above script
in Query Analyzer against your databse?
It might help if you were able to create (in your test db) a slightly
larger and more representative set of rows. It shoul be not too big for
posting, but big enough to replicate the problem; then get the actual
values in the update statement that your console simulation program sends
(use profiler to capture the actual statement sent, just to ensure it's
not an ADO or interfacing issue) when this problem happens. Then, first
try what happens if you execute the exact same statement in Query
Analyzer; if that doesn't show you where the problem is, post a script
here that enables me to insert the exact same values in my copy of your
tables and execute the exact same update statement.
Also, some followup questions:
* Just to make sure we're not misunderstanding each other: what data is
not committed? Is the feeds table unchanged after the update statement has
run, or is the feeds table updated but are the expected changes to the
markets table not performed?
* You write: "I am very sure that it is one row update not multiple row".
How did you verify this? After all, the following two snippets from your
code appear to be inconsistent:
>Create unique clustered index Feeds_idx on Feeds(marketno,symbol)
>GO
(...)
> /*get the market number which is the unique ID*/
> SELECT @Symbol = symbol,@nMarketNo = marketno FROM inserted
Both the feeds table and the marketdef table allow the addition of
multiple symbols with the same marketno. The update statement you posted:
>UPDATE feeds SET bid=%.04f, ask=%.04f, midPrice=%.04f WHERE marketno=%s
has only the marketno in the WHERE clause, so all symbols in that market
will be affected. This won't produce errors in the part of the trigger
thata gets executed for this update, but this part of the trigger:
> SELECT @Symbol = symbol,@nMarketNo = marketno FROM inserted
will fill @Symbol and @nMarketNo with the vallues of just one row in the
set; the trigger will do nothing for the other rows updated.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Hugo Kornelis: "Re: Multiple of 4? Better performance?"
- Previous message: Mal .mullerjannie_at_hotmail.com>: "RE: I'm not Trigger happy :-)"
- Next in thread: Jonathan Chong: "Re: Why data could not be committed into table?"
- Reply: Jonathan Chong: "Re: Why data could not be committed into table?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
