Press "Enter" to skip to content

Why And When To Use Pre-Update and Pre-Insert Triggers In Oracle Forms

Whenever we commit after entering data in Oracle Forms, many triggers fires during this event and also Pre-Update and Pre-Insert triggers fires just before inserting or updating the record into table. We can write Pre-Update and Pre-Insert triggers on particular block to just allow the process or to stop the process by using “Raise Form_Trigger_Failure” command, the following are the some examples:
Suppose you have a data block and there is a field which need to be validate just before inserting the record, then you must write the Pre-Insert trigger for this purpose, below is an example:
Declare
v_avl_qty number;
Begin
   Select avl_qty into v_avl_qty
     From stock_inhand
    Where Item_Code = :Block1.item_code;
if v_avl_qty < :Block1.qty_issued then
Raise Form_Trigger_Failure;
— Execution stopped…
end if;
— Else insertion will take place…
End;

And now I am giving you another example, suppose there is a field which need to be assigned from database just before the updation of the record, then you must write a Pre-Update trigger for this purpose, below is an example:

Declare
   v_value varchar2(10);
Begin
   Select a_value into v_value
     From a_table
    Where b_value = :Block1.b_value;
— Assign this value to block item
:Block1.a_value := v_value;
— you can assign any others value to any field just before updation or insertion like:
:Block1.create_date := Sysdate;
Exception
  when others then
     — After any error or no data found you still want to continue then you can use only Null; statement
     Null;
     — and any other value to any field you can still assign
:Block1.create_date := Sysdate;
End;

2 Comments

  1. Venu Venu January 15, 2016

    i have a master detail form. In the detail block i have 10 records with a check box for each line. I want to insert records into the detail block for which the check box is checked. How to achieve this ?

  2. V. Kapoor V. Kapoor January 15, 2016

    What you can do is… before performing commit_form you must clear those records from detail block which are not checked and after that your perform commit_form. Suppose you have a Save button in this master detail form and put the following code into when-button-pressed trigger of that button:

    Begin
    Go_block('yourdetailblock');
    First_record;
    Loop
    If Not Checkbox_Checked('yourdetailblock.yourcheckbox') then
    if :system.last_record = 'TRUE' then
    Clear_Record;
    Exit;
    Else
    Clear_Record;
    End if;
    Else
    if :system.last_record = 'TRUE' then
    Exit;
    End if;
    Next_Record;
    End if;
    End Loop;
    End;

Comments are closed.