FRM-40401 no changes to save after WHEN-CREATE-RECORD trigger

Question

I googled this but, unfortunately, could not find any solution.

I have a simple form (Oracle Forms Builder 10g) with a single block. The form is written in Oracle EBS style, that is, the block is based on a view that fetches the base table fields together with the rowid and DML events (on-insert, on-update etc. triggers) are handled by a table handler package.

The functionality I wanted to add was the following: when a user creates a new record, the form automatically suggests values for all fields in the form. So, I created a WHEN-CREATE-RECORD trigger that calculates the field values and assigns them. All, except the primary key wich is based on a Sequence and is handled by the package.

Everything runs OK when I create the new record but when I attempt to save it, all I get is a FRM-40401 "no changes to save" error and nothing happens.

I tried to trace the error and it seems like the form considers the record as NEW with no changes on it. This happens even if I try to explicitly alter the record status to INSERT.

I already tried to change the default behaviour to STANDARD.COMMIT (created an ON-COMMIT trigger for that) but this did not dfix anything.

For the record, I tried to make the form table-based, getting rid of table handlers and leaving all DML to Forms. I still get FRM-40401.

I can't understand what is going wrong, any ideas please?

Answer 1

The record status is reset to NEW after the when-create-record trigger completes. This normally makes sense, since you are effectively setting the default values for items, but the user hasn't actually entered any data yet.

You need something to mark the record for insert after the trigger has finished - normally the user would do this when they enter some data into the record. If you want the user to be able to save the record without changing anything in it, you could perhaps add something to the save button to do a no-change assignment, e.g.

:MYBLOCK.ANYITEM := :MYBLOCK.ANYITEM;

This would cause the record to be marked for insert.

Answer 2

OK, for the time being I used the classic TIMER workaround and everything works as it should:

PACKAGE body form_timers IS

 PROCEDURE CREATE_NEW_RECORD;

  procedure do_create(name varchar2) is
        timer_id TIMER;    
    Begin        
      timer_id := CREATE_TIMER(name,1,NO_REPEAT);
    End;

  procedure expired is
        expired_timer CHAR(20);
    BEGIN
        expired_timer:=GET_APPLICATION_PROPERTY(TIMER_NAME);
        IF expired_timer='CREATE_NEW_RECORD' THEN
             CREATE_NEW_RECORD;
--      ELSIF expired_timer='T2' THEN 
--          /* handle timer T2 */ NULL;
        ELSE 
            NULL;
        END IF;
    END;

 PROCEDURE CREATE_NEW_RECORD IS
    /* create record logic goes here */
    END;

END;

... but still, I'd like to know why this behaviour occurs.