Re: Oracle Trigger causing change error in Access. help.

Brad Pybus wrote on 05-Mai-99 23:26:06:
>I have an Oracle database using an Oracle ODBC driver to connect to an
>MS Access front end.

>I wrote a "BEFORE" trigger "FOR EACH ROW" that will do the following:

>:NEW.RecordChangedDate := SYSDATE;

>--------------

>The problem is, when I open the table in Access, and edit a field (not
>"RecordChangedDate") then move to another
>record and edit that same field, I almost always get the following
>error:

> The data has been changed.

> Another user edited this record and saved changes before you
>attempted to save your changes.

> re-edit the record.

>I then try to edit that field again and it works just fine.
>I know its being caused by the trigger because when I delete the
>trigger, the problem goes away.

>How do I always get the Oracle database to update the timestamp for a
>record when somebody edits the record without causing this error?

Hello Brad,

I think it's because Access uses optimistic locking. That means Access checks if the record has been changed by another user before doing commit or the like.
One possible solution might be to create a view of your table without the RecordChangeDateand using this with Access. So Access cannot complain about a change of this field.
You will have to create a before insert trigger to initially set the change date since Access cannot populate the fiel using the view.

Another way might be to disable the optimistic locking in Access but I don't know Access well enough to tell if and how this is possible.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |