Re: Forms 4.5 How- to Help requested

"Guy" <guycorbett_at_my-deja.com> wrote in message news:4be5d6aa.0112100306.2b3fd847_at_posting.google.com...
> I have a form based on a base-table block, the where clause excludes
> records based on a condition in another table. Inserting a new record
> is performed by the usual adding of detail to a blank row and then
> saving the block, on a normal insert a post-insert trigger handles the
> other table so that the record will now appear in this query. On some
> occasions the user tries to add one of the records already in the base
> table but excluded by the where clause. This is currently prevented
> by a pre-insert trigger checking the condition and raising an error.
> The users require a change to allow them to add these records without
> any other changes to the way the form works. I can track this
> condition in the pre-insert trigger and perform the update in the
> other table but cannot work out how to prevent forms continuing to
> attempt to insert the record and consequentially getting a ORA-40508
> because the record already exists in the base table. Attempts to get
> any of the triggers to requery the block rather than doing the insert
> give a FRM-40737 (Illegal restricted procedure GO_block in ...
> trigger).
> Is there a way to prevent the attempted insert of the individual row
> whilst still processing any other inserts or amendments on the screen
> or will I have to change the way the screen operates?
> Any helpful suggestions appreciated.

Code an on-insert trigger for the block. In it, do something like this:

DECLARE   CURSOR base_table_cursor IS
  SELECT 'x'
  FROM <base_table_name>
  WHERE <unique conditions>;
  v_dummy char(1);
BEGIN
  OPEN base_table_cursor;
[Quoted]   FETCH base_table_cursor INTO v_dummy;
[Quoted]   IF base_table_cursor%FOUND THEN

     --
     -- Record already exists
     --
     CLOSE base_table_cursor;
  ELSE
     --
     -- Continue normal insert by Forms
     --
     CLOSE base_table_cursor;
     INSERT_RECORD;