Update table using multi record block in post forms commit

Question

what I wanted is to update my table using values from a multi record block and here is what I tried in post forms commit:

BEGIN
    FIRST_RECORD;
    LOOP 
        UPDATE table1         
        SET ord_no = :blk.new_val;
        EXIT WHEN :SYSTEM.LAST_RECORD='TRUE';
        NEXT_RECORD;
    END LOOP;
END;

but when I save I got an error

FRM-40737: Illegal restricted procedure
FIRST-RECORD in POST-FORMS-COMMIT trigger.

Answer 1

OK, a few things to talk about here

1) I'm assuming that 'table1' is NOT the table on which the block is based. If the block was based on table1, simply as the user edits entries on screen, then when you do a COMMIT_FORM command (or the users clicks Save) then the appropriate updates will be done for you automatically. (That is the main use of a database-table based block).

2) So I'm assuming that 'table1' is something OTHER than the block based table. The next thing is that you probably need a WHERE clause on your update statement. I assume that you are updating a particular in table1 based on a particular value from the block? So it would be something like:

update table1
set ord_no = :blk.new_val
where keycol = :blk.some_key_val

3) You cannot perform certain navigation style operations when in the middle of a commit, hence the error. A workaround for this is to defer the operation until the completion of the navigation via a timer. So your code is something like:

Declare
  l_timer timer;
Begin
  l_timer := find_timer('DEFERRED_CHANGES');
  if not id_null(l_timer) then
    Delete_Timer(l_timer);
  end if;
  l_timer := Create_Timer('DEFERRED_CHANGES', 100, no_Repeat);
End;

That creates a timer that will fire once 100ms after your trigger completes (choose name, and time accordingly) and then you have your original code on a when-time-expired trigger.

But please - check out my point (1) first.