Re: "record not found or changed by another user"

was "trigger causes exception"

This turns out to really be a datasnap issue, so I'm going to set follow ups
to there.

"Brad White" <bwhite at inebraska.com> wrote in message
news:43614488$1@xxxxxxxxxxxxxxxxxxxxxxxxx
> Using D2005,
> ClientDataSets and ADO,
> SQL Server 2000, version 8.00.760
>
> Sporadically we will get an exception in ApplyUpdates:
> "record not found or changed by another user"
>

This turns out to be another case of a red-herring message.
(Red herring = misdirection
That means a message that looks like it is giving you useful info
but really sends you chasing off in the wrong direction)

The resolver that the CDS uses can return this message any time a query
returns (0 row(s) afected), even if 0 is returned for some other reason,
like
nothing to do.

It's tricky to track down because the "0 rows" doesn't have to be the last
message.
And it doesn't give the exception every time it gets a "0 rows".

So a trigger that attempts an insert/update but doesn't, will have 0 rows
affected
and can return this exception.

>From our example:
IF UPDATE(ABA)
BEGIN
INSERT INTO ChangeHistory (some column names)
SELECT some columns, i.DocumentID, d.ABA, i.ABA FROM deleted as
d, inserted as i
WHERE d.ABA <> i.ABA

For obscure reasons it UPDATE returns True if the inserted data does not
fill the field.
So it goes into our IF clause even if the data has not been changed.
But the WHERE catches the fact that they are the same and you correctly get
no rows inserted.
This results in the (0 row(s) afected) line being returned.

You can see these messages in Query Analyzer.

The resolver wasn't expecting results back from queries that *it* didn't
run.

Our solution was
SET NOCOUNT ON
This prevents the trigger from returning any row count messages. CDS no
longer
throws spurious exceptions.

Your problem may not be caused by a trigger. It could be any message of "0
rows returned"
from any source invoked by your update.
--
HTH,
Brad.

Vote for CodeRush in future versions of Delphi
http://qc.borland.com/wc/wc.exe/details?reportid=9138


.

Relevant Pages

  • "on exception" statement not firing within stored procedure when called from trigger
    ... I am experiencing a problem whereby ON EXCEPTION statements are ... not firing within stored procedures when the stored procedures ... are invoked from a trigger. ... create table tBar ) lock mode row; ...
    (comp.databases.informix)
  • Re: Error Handling Coming Out of a Trigger
    ... http://www.oledbdirect.com - The fastest way to access MS SQL Server, ... I have an after update trigger on a table. ... would have caused an exception in the calling application; ... FErrorMessage:= ...
    (borland.public.delphi.database.ado)
  • Re: [RFC] hwbkpt: Hardware breakpoints (was Kwatch)
    ... meaning that neither notifier will return NOTIFY_STOP. ... If it's a ptrace exception it also ... as a special case for a ptrace trigger. ...
    (Linux-Kernel)
  • Error Handling Coming Out of a Trigger
    ... I have an after update trigger on a table. ... enforce a business rule of not allowing a zero quantity. ... would have caused an exception in the calling application; ... FErrorMessage:= ...
    (borland.public.delphi.database.ado)