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;
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;
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
Follow @foxinfotechI 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.
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 ?
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;