Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Monday, 18 April 2016

Oracle Form Triggers

A trigger is a program unit that is executed (fired) due to an event. You can use triggers to add or modify form functionality in a procedural way. These events include the following:

Query-related events
Data entry and validation
Logical or physical navigation
Internal events in the form
Errors and messages

Trigger Components

There are three main components to consider when you design a trigger in Forms Builder

Trigger type
Trigger code
Trigger scope

Trigger Type

The trigger type determines which type of event fires it. There are more than 100 built-in triggers, each identified by a specific name. Triggers are mostly fired by events within a form module.

Trigger Code

The code of the trigger defines the actions for the trigger to perform when it fires.

Trigger Scope

 The scope of a trigger is determined by its position in the form object hierarchy.   There are three possible levels
Form level: The trigger belongs to the form and can fire due to events across the entire form
Block level: The trigger belongs to a block and can fire only when this block is the current block
Item level: The trigger belongs to an individual item and can fire only when this item is the current item

Trigger Execution Hierarchy
When there is more than one trigger of the same type, you can alter the firing sequence of a trigger by setting the execution hierarchy (EH) trigger property. This diagram shows how setting EH affects the firing order of triggers:

1.     Fires first
2.     Fires second
3.     Fires third
4.       Fires independently

Creating Trigger
In the Object Navigator, select the Triggers node of the form, block, or item that will own the trigger. Alternatively, you can select an item in the Layout Editor if you are defining an item level trigger.

Use the PL/SQL Editor to define the trigger code.
Click the Compile icon in the PL/SQL Editor to compile the trigger. This displays immediate feedback in the form of compilation error messages, which you can correct. If the trigger compiles correctly, a message displays in the lower right corner of the editor.


Using Variables

Query Triggers
    Whenever form server receives signals for Query whether internally or externally, it fires certain triggers in specific sequence. Its understanding is really very useful if we need to customize the query process as necessary, and supplement the results returned by a query. The sequence of the various triggers which are fired under Query operation are:

PRE-QUERY: Used to construct WHERE clause
ON-QUERY: Used to construct SELECT statement and executes it
POST-QUERY: Used to populate non-database items 

Query Triggers Detailed Process

The Validation Levels:
Forms performs a validation process at several levels to ensure that records and individual values follow appropriate rules. If validation fails, then control is passed back to the appropriate level, so that the operator can make corrections. Validation occurs at:

Form Level
Block Level
Record Level
Item level

Validation Methods
There are few methods which can be implemented to perform validations. Some of these are:
Using Object Properties
Using Triggers
Using List of Values

Using Triggers
There are few triggers that fire due to validation, which let you add your own customized actions. There are also some built-in subprograms that you can call from triggers that affect validation. These triggers are:
Navigation occurs when the user or a trigger causes the input focus to move to another object. In addition to the visible navigation that occurs, some logical navigation takes place. This logical navigation is also known as internal navigation.

Example: When you enter a form module, you see the input focus in the first enterable item of the first navigation block. You do not see the internal navigation events that must occur for the input focus to enter the first item. These internal navigation events are as follows:
Entry to form
Entry to block
Entry to record
Entry to item

Controlling Navigation using triggers
The navigation triggers can be subdivided into two general groups:
Pre- and Post- navigation triggers
When-New-<object>-Instance triggers
The Pre- and Post- navigation triggers fire during navigation, just before entry to or just after exit from the object specified as part of the trigger name.
The When-New-<object>-Instance triggers fire immediately after navigation to the object specified as part of the trigger name.

Transaction Processing
While applying a user’s changes to the database, Forms Builder enables you to make triggers fire in order to alter or add to the default behavior.
When Forms is asked to save the changes made in a form by the user, a process takes place involving events in the current database transaction. This process includes:
 Default Forms transaction processing: Applies the user’s changes to the base tables
Firing transactional triggers: Needed to perform additional or modified actions in the saving process defined by the designer When all of these actions are successfully completed, Forms commits the transaction, making the changes permanent.

Some of Forms Builder built-in subprograms provide the same type of run-time status information that built-in system variables do. Some of the Get object property Built – in are:
Some of the Set object property Built – in are:

Share this article :


  1. Hello There,

    Such vivid info on the Oracle Form Triggers Flabbergasted! Thank you for making the read a smooth sail!

    I need to insert 10lakhs record using bulk collect I got an error in say 96000th record how to solve it????? Using stored procedure and collections
    Kindly share the program if anyone knows.

    Great effort, I wish I saw it earlier. Would have saved my day :)

    Thanks a heaps,

    1. You can use the SAVE EXCEPTION clause in FORALL DML and capture exceptions from oracle standard collection SQL%BULK_EXCEPTIONS(index).ERROR_CODE

  2. Halo,

    Fully agree on Oracle Form Triggers . We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    Currently I'm trying to make a test using create job as a way to have multiple process on database starting in a loop.
    Basically I'm getting different behaviors when testing on different database machines. I'll define the machine names are A and B.
    If I started running the anonymous block below in the machine A for a loop to create 3 multiple jobs, is working fine, i.e, the create job using enabled parameter, created the job and automatically start:
    L_job_name VARCHAR2(100);
    L_comments VARCHAR2(240);
    L_pls_block VARCHAR2(32000);
    L_thread NUMBER := 1; -- count of jobs
    L_max_threads NUMBER := 3; -- max number of jobs to be started
    L_lot_id NUMBER := 1234; -- add any number just to represent a lot

    while L_thread <= L_max_threads loop
    L_job_name := 'job_' || L_lot_id || L_thread;
    L_comments := 'Job test ' || L_lot_id || ' and thread ' || L_thread;
    L_pls_block := 'BEGIN logger.log(''job running of thread' || L_thread || '''); END;';
    dbms_scheduler.create_job(job_name => L_job_name,
    job_type => 'PLSQL_BLOCK',
    job_action => L_pls_block,
    start_date => SYSTIMESTAMP,
    comments => L_comments,
    enabled => true);


    L_thread := L_thread + 1;
    end loop;

    but if I start the same code in the machine B, nothing is happening and I need to uncomment the line about DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);

    I did another test in the same machine B using enabled FALSE when creating the job and during the call from DBMS_SCHEDULER.RUN_JOB changed to false in the user_credentials but only one job runned, i.e, the result was logged from just one job, instead of the 3 jobs that had most be created.

    Do you have an idea about what I need to look? Is there something related about database setup, credentials that I need to take a look?

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thank you,

  3. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check

  4. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.

  5. Excellent blog!!! I really enjoy to read your post and thanks for sharing!
    Online Solicitation Of A Minor
    Abogado De Divorcio En Virginia

  6. Nice Blog!! Thanks for sharing this content, RH Soft Tech is India’s best sap online training and online professional IT courses training provider. SAP FICO Online Training in Mumbai