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;

Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

2 Comments

  1. 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. 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;

Leave a Reply

Your email address will not be published. Required fields are marked *