>
> Have I missed something? Has this ever been discussed in the forum?
> Wouldn't it be a great capability? Is it possible to hack the
> active/inactive flag by changing a system table value as part of a
> transaction? (I'll be trying this next.)
>
> P.S. The usual idea of adding a "ChangedBy" column to identify the
> source of the change doesn't help here, because its not applicable
> when deleting rows, and as I said, I do a lot of that :-)
>

Can you use a separate table to log the changed by?

You can use the CURRENT_CONNECTION context variable to note who did
the action. Then just set your triggers to fill this table when a
change occurs. Obviously you can not declare the foreign key or you
will not be able to delete the records, but you might still want to
index that field. Then when you read the list of changed records that
were not made by your connection, you know which records to retrieve.
If any of those records are not returned in a query, then they were
obviously deleted and you should delete your local copy.

Very wordy solution, lets make a simple example

Here are some tables:

Customer (ID, FirstName, LastName, etc);
ChangeLog (ChangedBy, CustomerID, ChangedAt);

Do not declare ChangeLog.CustomerID as a foreign key.

Now you place triggers on all events in Customer that essentially do this.

insert into ChangeLog(ChangedBy, CustomerID) values
(CURRENT_CONNECTION, NEW.CUSTOMERID, 'NOW');

** obviously that is OLD.CUSTOMERID in the delete trigger, and I
recommend you don't bother with allowing the IDs to change.

OK, now "periodically", you can do this.

Select cl.CustomerID, c.ID, c.Firstname, c.LastName, c.etc
from ChangeLog cl
left join Customer c on (cl.CustomerID = c.ID)
where cl.ChangedBy <> CURRENT_CONNECTION
and cl.ChangedAt > :LastPolledTime

When you get the records back, any that are missing (c.ID is null) you
delete from your local cache, any others you replace if exist or
insert if dont exist.

We take this one step further and have a device table, and our
triggers put a note in for each device as to changes they may be
interested in.

Of course a simpler way to do this whole exercise is to use events,
but that is not an option if you have to get through firewalls.

Adam