Re: Why data could not be committed into table?
From: Jonathan Chong (jonathan_at_3exp.com)
Date: 10/29/04
- Next message: Jonathan Chong: "Re: Why data could not be committed into table?"
- Previous message: avnrao: "Re: Execution plans"
- In reply to: Mal .mullerjannie_at_hotmail.com>: "Re: Why data could not be committed into table?"
- Next in thread: Jonathan Chong: "Re: Why data could not be committed into table?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 29 Oct 2004 12:58:32 +0800
OK. Latest update. It is something to do with accessing the inserted and
deleted in the update trigger.
When I took out the portion of "IF UPDATE(high)" and "IF UPDATE([low])" that
accessing inserted and deleted, OR replaced with this:
IF UPDATE(high)
BEGIN
print '1'
END
IF UPDATE([low])
BEGIN
print '2'
END
Everything back to normal, data can be updated.
WHY????????
/******************************************trigger**************************
*************/
if exists (select * from sysobjects where id =
object_id('dbo.Feeds_update_trg') )
drop trigger dbo.Feeds_update_trg
GO
CREATE TRIGGER Feeds_update_trg
on Feeds
for update
as
DECLARE @newLastDone decimal(15,5)
DECLARE @PrevClose decimal(15,5)
DECLARE @nMarketNo int
DECLARE @Symbol varchar(30)
DECLARE @nDirection tinyint
DECLARE @CurrMidPrice decimal(15,5)
DECLARE @PrevMidPrice decimal(15,5)
DECLARE @MidPriceDiff decimal(15,5)
SET NOCOUNT ON
IF UPDATE(midPrice)
BEGIN
/*check if last done change*/
SELECT @CurrMidPrice=1.888,@newLastDone = 1.989,@PrevClose=1.892
SELECT @PrevMidPrice = 1.999
IF @CurrMidPrice <= 0
BEGIN
SET NOCOUNT OFF
RETURN --do nothing
END
/* Get the abs different value of current and previous mid price*/
SELECT @MidPriceDiff = abs(@CurrMidPrice-@PrevMidPrice)
/*Determine the up/down direction*/
IF @newLastDone < @PrevClose
SET @nDirection = 1 /*down*/
ELSE IF @newLastDone > @PrevClose
SET @nDirection = 0 /*up*/
ELSE IF @newLastDone = @PrevClose
SET @nDirection = 2 /*unchg*/
exec ApplyRules_sp 'symbol',4,1.6879,1.893,1.89
END
IF UPDATE(high)
BEGIN
IF(SELECT offline FROM inserted) = 1 /*manual update*/
BEGIN
UPDATE markets
SET markets.high = inserted.high * POWER(10,markets.decplaces)
FROM inserted,markets
WHERE inserted.marketno = markets.marketno
END
END
IF UPDATE([low])
BEGIN
IF(SELECT offline FROM inserted) = 1 /*manual update*/
BEGIN
UPDATE markets
SET markets.[low] = inserted.[low] * POWER(10,markets.decplaces)
FROM inserted,markets
WHERE inserted.marketno = markets.marketno
END
END
SET NOCOUNT OFF
GO
"Mal .mullerjannie@hotmail.com>" <<removethis> wrote in message
news:1D2C8D0B-7A3A-4DD1-A31B-C550DCAC4BE2@microsoft.com...
> If the app uses sa you should be fine with access.
>
> Some questions to help me with info :
>
> Does the client app use prepared SQL ? or dynamic sql ?
> Can you execute other statements via this client app ?
> Howcome you do not use SQL replication ?
> Do this problem with the updates occur all the time , or only once ?
> One more...
> This update statement , could you paste it ?
>
> "Jonathan Chong" wrote:
>
> > It was using "sa" and I did check the permission. Everything seems to be
OK
> > on permission level. Where else should I check?
> >
> > "Mal .mullerjannie@hotmail.com>" <<removethis> wrote in message
> > news:DD9E7DFB-5096-4547-BD33-C05B7893570B@microsoft.com...
> > > The only thing that I can think of...
> > >
> > > If you run the update through query analyzer , the statement is ran in
a
> > > transaction by default, when the query completes , transaction
commit.
> > >
> > > If you are using ado connection maybe the update is stil done and
executed
> > > thus showing in profiler, but some problem occur, and transaction is
not
> > > commited.
> > >
> > > It could also be that your ADO connection do not have sufficient
access
> > > rights and there's no return error msg, to tell you that in the other
app.
> > >
> > > My 2cents, hope it helps.
> > > "Jonathan Chong" wrote:
> > >
> > > > I have a set of database which is imported/duplicated from
production
> > box.
> > > > That means all objects are inherited from production box including
the
> > data.
> > > >
> > > > This is the problem situation.
> > > > We have a client application that keep receiving data from server
> > > > application and instantly update the data into the database.
However,
> > the
> > > > data just could not be committed into the table. I said committed
b'cos
> > the
> > > > update statement could be traced on profiler. I used the same
statement
> > from
> > > > profiler and execute it using QA and data could be updated into the
> > table.
> > > >
> > > > To further prove that the client application is actually connected
to
> > the
> > > > correct database on correct server with correct authentication.
> > > > 1. I print out the connection string and it is correct.
> > > > 2. I changed the column name of the table and error messages keep
> > reported
> > > > in Event viewer saying "invalid column name...".
> > > >
> > > > Why data could not be committed into table when update statement
could
> > be
> > > > traced with profiler?
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > > >
> >
> >
> >
- Next message: Jonathan Chong: "Re: Why data could not be committed into table?"
- Previous message: avnrao: "Re: Execution plans"
- In reply to: Mal .mullerjannie_at_hotmail.com>: "Re: Why data could not be committed into table?"
- Next in thread: Jonathan Chong: "Re: Why data could not be committed into table?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
