Xref: alice comp.databases.oracle.tools:14201 Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!wuff.mayn.de!uniol!news From: "Marcel Claus" Newsgroups: comp.databases.oracle.tools Subject: Re: Problems with Forms using views Date: Tue, 07 Jul 1998 14:33:50 +0100 Organization: C. v. Ossietzky Universitaet Oldenburg - Fachbereich Informatik Lines: 53 Message-ID: <6nt4io$ok0@news.Informatik.Uni-Oldenburg.DE> References: <6nfteu$sos$1@pumba.class.udg.mx> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Mailer: Mozilla 4.05 [en] (WinNT; I) To: Wanderley Montefusco Ceschim Wanderley Montefusco Ceschim wrote: > We have a few forms that, after saving the changed data and trying to alter > the same data again, Oracle Forms presents the folowing error message : > > FRM-40654 : Record has been updated by another user. Re-query to see > change. > > We know that the problem is created by the fact that the data that are shown > to the user are being manipulated by database triggers that, for instance, > updates the primary key of the table with a value from a sequence. This way, > the data that the user sees on the screen are not those that are in the > table, because the trigger has changed it. > > One solution we found was to re-read the table using the POST-INSERT and > POST-UPDATE form triggers, replacing the on-screen values with those > existing in the table, synchronizing database and form. This solution is > based on a re-read using the ROWID of the table, which is a value that is > always brought back from the database after an insert operation and doesn't > change on an update operation. > > The problem gets hairy when the block we are talking about is really based > on a database VIEW, instead of being based on a table. In such case, the > ROWID value is not avaliable for us if such view is based on more than one > table. Thus, we cannot make the POST-INSERT and POST-UPDATE triggers to use > ROWID to re-query the data. > > We found an initial solution for this problem which is to use the primary > key of the tables used on the view to re-query the data. This would only > work if the view acting as a base for a block includes ALL columns from the > primary keys of ALL tables used in the join operation by the view. > > Finally, our questions are: > Is the requery operation for tables (using the ROWID) the best way to solve > this problem or there is a way to make Forms do it automatically for us? > Is the final solution the best we can get (using ALL columns from ALL PKs)? I had the same problem and I used an on-error-trigger catching the error FRM-40654. The trigger reloads the table raising the error. I dont know if this is faster than your way but it has the advantage that one on-error-triggers handles all tables and views of a form. The POST-XXXX triggers can be overwritten on block or item-level. I found no way to let Form do the stuff, but if someone mails you a solution I would be glad if you forward it to marcel.claus@informatik.uni-oldenburg.de Thank you Marcel