Three computers:  SERVER,  MARY,  JOSH.

Three complete copies of the database for each computer.  Each user
works on their own local copy of the database, synchronizing with the
server manually.  The server synchronizes hourly.

Let's say that Mary creates Invoice 123, then synchronizes.  The sync
script creates an update in Mary's local store and copies it to her
"out" store, which is a remote into an "in" store for her ID# on the
server.

The server's hourly job runs and copies pending updates from users to
local stores and loads them, then prepares updates for all users and
copies them to the server's "out" stores (linked by users as "in"
stores).

So far, only Mary and the Server have the new Invoice 123.

Josh synchronizes, and after copying then loading the updates from the
server, now also has Invoice 123.  Everyone is happy.

Then Mary realizes she doesn't need Invoice 123 (the order was canceled
or something).  The business rules do not dictate that she needs to
keep the invoice around, so she deletes Invoice 123.

Around the same time, Josh looks at Invoice 123 and sees something that
needs to be changed, and makes the change.  Both Mary and Josh run
their synchronization script, which sends Mary's DELETE message and
JOSH's UPDATE message to the server.

Now the server loads updates.

If Mary's DELETE command gets loaded first, the server deletes invoice
123, right?  Then Josh's UPDATE command tries to be processed, but
Invoice 123 no longer exists.  So the hourly job breaks because it
can't find the primary key belonging to Invoice 123.


Should I prevent deletions of all tables and simply deactivate records
instead (add an "Active" BOOLEAN field)?  It would mean a lot of work
throughout the application, but if that will stop the errors I'm
getting I'll dig in and do it.


--
David Cornelius
CorneliusConcepts.com

David,

A possible way to handle this is to define an ERROR UPDATE Trigger on
the table invoices table (not tested):

CREATE TRIGGER "CondUpdate" ERROR UPDATE
ON "Invoices"
BEGIN
  IF NOT (LOADINGUPDATES() AND ERRORCODE=1007) THEN
    RAISE;
  END IF;
END


The trigger will "eat" all exceptions of that type, but only when
loading updates.

--
Fernando Dias
[Team Elevate]

David,

Actually, I tested that and it doesn't work because the ERROR UPDATE
trigger doesn't fire for this type of update errors. Sorry.

--
Fernando Dias
[Team Elevate]

> If Mary's DELETE command gets loaded first, the server deletes invoice
> 123, right?  Then Josh's UPDATE command tries to be processed, but
> Invoice 123 no longer exists.  So the hourly job breaks because it
> can't find the primary key belonging to Invoice 123.
>
>
> Should I prevent deletions of all tables and simply deactivate records
> instead (add an "Active" BOOLEAN field)?  It would mean a lot of work
> throughout the application, but if that will stop the errors I'm
> getting I'll dig in and do it.


Tim,

Should all remote updates be copied to the same local store on the server?
Or is the above scenario even a problem at all?

--
David Cornelius
CorneliusConcepts.com

David,

<< If Mary's DELETE command gets loaded first, the server deletes invoice
123, right? >>

Correct.

<< Then Josh's UPDATE command tries to be processed, but Invoice 123 no
longer exists.  So the hourly job breaks
because it can't find the primary key belonging to Invoice 123. >>

Correct.  Fernando proposed that I include this type of load error
(update/delete primary key not found) in the ERROR triggers, and I intend to
do so in the next build.  That will allow you to trap such instances and
either ignore them or log them to another table for later review.

<< Should I prevent deletions of all tables and simply deactivate records
instead (add an "Active" BOOLEAN field)?  It would mean a lot of work
throughout the application, but if that will stop the errors I'm getting
I'll dig in and do it. >>

No, there's no need for anything like that.   The above change should give
you what you want.

--
Tim Young
Elevate Software
www.elevatesoft.com

> << Then Josh's UPDATE command tries to be processed, but Invoice 123 no
> longer exists.  So the hourly job breaks
> because it can't find the primary key belonging to Invoice 123. >>
>
> Correct.  Fernando proposed that I include this type of load error
> (update/delete primary key not found) in the ERROR triggers, and I intend
> to do so in the next build.  That will allow you to trap such instances
> and either ignore them or log them to another table for later review.


OK, thanks.  I will look for that in the next build then.

--
David Cornelius
CorneliusConcepts.com