TRIGGERS questions

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.