Triggers - A Depth View...

TRIGGERS
Triggers are block of PL/SQL code that you write to perform your specific tasks.They  are pre-defined runtime events

for which oracle will provide you to associate a trigger.All the trigger name correspond to these events.The events

which can be trapped are GUI events  as well as transactional events.

What are all theValid Statements in Trigger Text
•    Select statements
•    DML statements
•    Restricted built-in subprograms
•    Unrestricted built-in subprograms

What is Restricted Built-in ?
Restricted built-ins are those built-ins which initiate navigation like NEXT_ITEM etc
and also those that involve database transactions,such as CREATE_RECORD.

Different Trigger types

1.    BLOCK PROCESSING TRIGGERS
   
•    WHEN-CLEAR-BLOCK
Fires just before the oracle forms clears the data from the current block.It does not fire when the Oracle forms 

clears the current block during the CLEAR_FORM event.
Defn Level         :         Form or Block.
Enter-Query Mode     :        Yes
Legal Commands    :        SELECT statements,unrestricted built-ins

When to use ?
•    use this trigger to perform action everytime oracle forms flushes the current block.For example you might

want to perform automatic commit whenever this condition occurs
•    The value of  SYSTEM.RECORD_STATUS  is unreliable because there is no current record. An alternative is to

use GET_RECORD_PROPERTY to obtain the record status.because GET_RECORD_PROPERTY requires you to reference a specific

record,its value is always accurate.
Failure of Trigger    :        No effect of block clearing
Fires in        :        CLEAR_BLOCK
                    COUNT_QUERY
                    ENTER-QUERY
                    Open the Query.

•    WHEN-CREATE-RECORD
Fires when Oracle forms creates a new record or the form operator presses the [insert  key] or while scrolling he

scrolls to the last record in the set.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,unrestricted built-ins

When to use ?
use this trigger to perform a action every time when oracle attempts to create  a new record.   
Failure of the Trigger    :        Prevents the New record to be created and
                    the control returns to previous location if
                    possible.
Fires in        :        CREATE_RECORD.

•    WHEN-DATABASE-RECORD
Fires when the oracle marks the record as INSERT or UDATE.That is the oracle forms through validation process

determines that record should processed as INSERT or UPDATE in the Next Post or Commit Process.This Occurs after a

Operator modifies the first item of a record and tries to navigate out of the Item.
Defn Level         :        Form or Block
Enter-Query Mode      :        No
Legal Commands    :        SELECT statements,unrestricted built-ins.

When to Use ?
Use this trigger to perform a action when oracle forms marks a record as Insert or Update
Failure of Trigger    :        Nothing happens.

•    WHEN-REMOVE-RECORD
Fires whenever the operator or the application Clear record or Delete the Record.
Defn Level        :        Form,Block and Item
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,unrestricted built-ins

When to use ?
Use this trigger to perform a action when oracle forms tries to clear a record or delete record.
Failure of Trigger    :        Oracle Forms navigates to Block level with
                    or without validation and tries to put the
                    cursor at the target block.
Fires in        :        CLEAR_BLOCK
                    DELETE_RECORD
                    PREVIOUS_RECORD.

2.     MASTER-DETAIL TRIGGERS

•    ON-CHECK-DELETE-MASTER
Oracle forms creates this trigger automatically when we define a master-detail relation ship ans set Master Deletes

property to NON- ISOLATED.It fires when a attempt is made to DELETE  record in Master-block  of a master-detail

relation-ship.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,unrestricted built-ins
                    and DML statements.

   
When to use ?
It is used to check whenever you try to delete the master record,whether any detail records of the master exists, in

case any details are present then the Master record is prevented from deletion.

Failure of Trigger    :        Prevents the deletion of the Master record.
Fires in        :        MASTER-DETAIL COORDINATION.

•    ON-CLEAR-DETAIL
Fires whenever a coordination causing event occurs in Master-Block.The coordination causing event is the event which

makes the a different master record as the current record, in the Master Block.
Defn Level        :        Form or Block.
Enter-Query Mode    :        No
Legal Commands    :        Any commands including restricted built ins

Failure of Trigger    :        Causes the coordination-causing operation and any
                    scheduled coordination triggers to abort.
Fires in        :        MASTER-DETAIL COORDINATION.

•    ON-POPULATE-DETAIL
Whenever we create a master-detail relation ship,oracle forms automatically will create this trigger.It fires when

oracle forms normally needs to populate the detail block in master-detail relation.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,PL/SQL,unrestricted and
                    restricted built-ins.
When to Use ?
•    Use this trigger to replace the default populate phase of a Query in Master-detail.
•    When the immediate Coordination is set this causes the details of the instantiated master to be populated

immediately. By default the Coordination is immediate.
•    WhenDeffered is coordination status,then this trigger fires,Oracle forms marks the blocks as needing to be

coordinated.
•    If you intend to manage the coordination on your own then there is built in called as the

SET_BLOCK_PROPERTY(COORDINATION_STATUS).
Note : This trigger will not fire if there is no ON-CLEAR-DETAILS trigger.
Failure of Trigger    :        Causes an inconsistent state in the Form
Fires in        :        MASTER-DETAIL COORDINATION.


3.     MESSAGE-HANDLING TRIGGERS

•    ON-ERROR
This trigger fires whenever a Oracle forms would normally cause an Error message to display.
Defn Level           :        Form,Block or Item
Enter-Query Mode    :        Yes
Legal Commands    :        SELECT statements,unrestricted built-ins
When to Use ?
•    Use this trigger to trap and recover from a error
•    To replace a default error message with a custom error message
Failure of Trigger    :        No effect

•    ON-MESSAGE
This trigger fires whenever oracle form would normally cause a message to display.This trigger will replace the

writing of the message to the message line.
Defn Level        :        Form,Block or Item
Enter-Query Mode    :        Yes
Legal Commands    :        SELECT statements,unrestricted built ins.
When to Use ?
•    Use this trigger to exclude or to replace a message with a custom message.The GET_MESSAGE built does not

return any value.
Failure of Trigger    :        No effect

3.     TRANSACTIONAL TRIGGERS

•    ON-CHECK-UNIQUE
This trigger fires during the commit operation,when oracle forms noramally checks that the primary key values are

unique for each INSERT or UPDATE in a base table.It fires once for each record UPDATED or INSERTED.This record

replaces the DEFAULT checking of UNIQUENESS for records,when PRIMARY key property of the record is set to true.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,PL/SQL and unrestricted
                    built ins
When to Use ?
To check the uniqueness of items before they are inserted or updated.To do the Default processing from this trigger

call the built in CHECK_RECORD_UNIQUENESS.
Failure of Trigger    :        No effect
Fires in        :        CHECK RECORD UNIQUENESS
                    POST AND COMMIT TRANSACTIONS.
•    ON-CLOSE
This trigger normally fires when the operator or an application closes a query.The query is closed by default when

all the records identified by query criteria have been fetched or when the operator or the application aborts the

query.