1.1 About Triggers and Processes
Triggers are blocks of PL/SQL code that you write to perform specific tasks. There are pre-defined runtime events for which you can create triggers. Trigger names correspond to these events. For ease of description, the terms events and triggers are synonymous in this chapter. In effect, an Oracle Forms trigger is an event-handler written in PL/SQL to augment (or occasionally replace) the default processing behavior.
A process is a series of individual, related events that occurs during a specific Oracle Forms Runform operation. Oracle Forms includes navigational, validation, and database transaction processes. To see a visual representation of Oracle Forms processes showing where each trigger fires, refer to the flowchart for the process named in the "Fires In" section for each trigger. All the flowcharts are in the Oracle Forms Reference Manual, Vol. 2, Chapter 8, "Processing Flow Charts".
1.2 SQL Statements in Trigger Text
The trigger descriptions in this chapter include a section called "Legal Commands." This section lists the types of statements that are valid in the indicated trigger type, including:
• restricted built-in subprograms
• unrestricted built-in subprograms
• SELECT statements
• Data Manipulation Language (DML) statements
Restricted built-in subprograms initiate navigation. They include built-ins that move the input focus from one item to another, such as NEXT_ITEM , and those that involve database transactions, such asCREATE_RECORD.
Restricted built-ins are illegal in triggers that fire in response to navigation, such as Pre- and Post- navigational triggers. Each built-in description includes a "Built-in Type" section that indicates whether the built-in is restricted or unrestricted. For more information, refer to the Oracle Forms Developers Guide, Chapter 7, "Writing Event Triggers."
While you can write a trigger that uses any DML statement, Oracle Corporation advises that you follow the recommendations that are stated for each trigger type. Using DML statements in certain triggers can desynchronize the state of records in Oracle Forms and rows in the database, and can cause unexpected results.
1.3 Trigger Tables
The following cross references are included to help you locate the triggers you need in each category.
1.3.1 Block Processing Triggers
1.3.1.1 When-Clear-Block
Fires just before Oracle Forms clears the data from the current block. Note that the When-Clear-Block trigger does not fire when Oracle Forms clears the current block during the CLEAR_FORMevent.
1.3.1.2 When-Create-Record
Fires whenever Oracle Forms creates a new record. For example, when the operator presses the [Insert] key, or navigates to the last record in a set while scrolling down, Oracle Forms fires this trigger.
1.3.1.3 When-Database-Record
Fires when Oracle Forms first marks a record as an insert or an update. That is, the trigger fires as soon as Oracle Forms determines through validation that the record should be processed by the next post or commit as an insert or update. This generally occurs only when the operator modifies the first item in a record, and after the operator attempts to navigate out of the item.
1.3.1.4 When-Remove-Record
Fires whenever the operator or the application clears or deletes a record.
1.3.2 Interface Event Triggers
1.3.2.1 When-Button-Pressed
Fires when an operator selects a button, either by way of a key, or by clicking with a mouse.
1.3.2.2 When-Checkbox-Changed
Fires whenever an operator changes the state of a check box, either by clicking with the mouse, or through keyboard interaction.
1.3.2.3 When-Custom-Item-Event
Fires whenever a VBX control sends an event to Oracle Forms.
1.3.2.4 When-Image-Activated
Fires when an operator double-clicks on an image item with the mouse.
Note that When_Image_Pressed also fires on a double-click.
1.3.2.5 When-Image-Pressed
Fires when an operator uses the mouse to:
• single-click on an image item
• double-click on an image item (note that When_Image_Activated also fires on a double-click)
1.3.2.6 When-List-Changed
Fires when an operator selects a different element in a list item or de-selects the currently selected element. In addition, if a When-List-Changed trigger is attached to a combo box style list item, it fires each time the operator enters or modifies entered text.
1.3.2.7 When-Mouse-Click
Fires after the operator clicks the mouse if one of the following events occurs:
• if attached to the form, when the mouse is clicked within any canvas-view or item in the form
• if attached to a block, when the mouse is clicked within any item in the block
• if attached to an item, when the mouse is clicked within the item
Three events must occur before a When-Mouse-Click trigger will fire:
• Mouse down
• Mouse up
• Mouse click
Any trigger that is associated with these events will fire before the When-Mouse-Click trigger fires.
1.3.2.8 When-Mouse-DoubleClick
Fires after the operator double-clicks the mouse if one of the following events occurs:
• if attached to the form, when the mouse is double-clicked within any canvas-view or item in the form
• if attached to a block, when the mouse is double-clicked within any item in the block
• if attached to an item, when the mouse is double-clicked within the item
Six events must occur before a When-Mouse-DoubleClick trigger will fire:
• Mouse down
• Mouse up
• Mouse click
• Mouse down
• Mouse up
• Mouse double-click
Any trigger that is associated with these events will fire before the When-Mouse-DoubleClick trigger fires.
1.3.2.9 When-Mouse-Down
Fires after the operator presses down the mouse button if one of the following events occurs:
• if attached to the form, when the mouse is pressed down within any canvas-view or item in the form
• if attached to a block, when the mouse is pressed down within any item in the block
• if attached to an item, when the mouse is pressed within the item
Note: The mouse down event is always followed by a mouse up event.
1.3.2.10 When-Mouse-Enter
Fires when the mouse enters an item or canvas-view if one of the following events occurs:
• if attached to the form, when the mouse enters any canvas-view or item in the form
• if attached to a block, when the mouse enters any item in the block
• if attached to an item, when the mouse enters the item
1.3.2.11 When-Mouse-Leave
Fires after the mouse leaves an item or canvas-view if one of the following events occurs:
• if attached to the form, when the mouse leaves any canvas-view or item in the form
• if attached to a block, when the mouse leaves any item in the block
• if attached to an item, when the mouse leaves the item
1.3.2.12 When-Mouse-Move
Fires each time the mouse moves if one of the following events occurs:
• if attached to the form, when the mouse moves within any canvas-view or item in the form
• if attached to a block, when the mouse moves within any item in the block
• if attached to an item, when the mouse moves within the item When-Mouse-Up
1.3.2.13 When-Mouse-Up
Fires each time the operator presses down and releases the mouse button if one of the following events occurs:
• if attached to the form, when the mouse up event is received within any canvas-view or item in a form
• if attached to a block, when the mouse up event is received within any item in a block
• if attached to an item, when the mouse up event is received within an item
Two events must occur before a When-Mouse-Up trigger will fire:
• Mouse down
• Mouse up
1.3.2.14 When-Radio-Changed
Fires when an operator selects a different radio button in a radio group, or de-selects the currently selected radio button, either by clicking with the mouse, or by way of keyboard selection commands.
1.3.2.15 When-Timer-Expired
Fires when a timer expires.
Note: Timers are created programmatically by calling the CREATE_TIMER built-in procedure.
1.3.2.16 When-Window-Activated
Fires when a window is made the active window. This occurs at form startup and whenever a different window is given focus. Note that on some window managers, a window can be activated by, say, clicking on its title bar. This operation is independent of navigation to an item in the window. Thus, navigating to an item in a different window always activates that window, but window activation can also occur independently of navigation.
1.3.2.17 When-Window-Closed
Fires when an operator closes a window using a window-manager specific Close command.
1.3.2.18 When-Window-Deactivated
Fires when an operator deactivates a window by setting the input focus to another window.
1.3.2.19 When-Window-Resized
Fires when a window is resized, either by the operator or programmatically through a call toRESIZE_WINDOW or SET_WINDOW_PROPERTY. (Even if the window is not currently displayed, resizing the window programmatically fires the When-Window-Resized trigger.) This trigger also fires at form startup, when the root window is first drawn. It does not fire when a window is iconified.
1.3.3.1 Function Key
Function key triggers are associated with individual Runform function keys. A function key trigger fires only when an operator presses the associated function key. The actions you define in a function key trigger replace the default action that the function key would normally perform.
The following paragraph shows all function key triggers and the corresponding Runform function keys :
Key-CLRBLK [Clear Block], Key-CLRFRM [Clear Form],
Key-CLRREC [Clear Record], Key-COMMIT [Accept],
Key-CQUERY [Count Query Hits], Key-CREREC [Insert Record],
Key-DELREC [Delete Record], Key-DOWN [Down],
Key-DUP-ITEM [Duplicate Item], Key-DUPREC [Duplicate Record],
Key-EDIT [Edit], Key-ENTQRY [Enter Query],
Key-EXEQRY [Execute Query], Key-EXIT [Exit],
Key-HELP [Help], Key-LISTVAL [List of Values],
Key-MENU [Block Menu], Key-NXTBLK [Next Block],
Key-NXT-ITEM [Next Item], Key-NXTKEY [Next Primary Key],
Key-NXTREC [Next Record], Key-NXTSET [Next Set of Records],
Key-PRINT [Print], Key-PRVBLK [Previous Block],
Key-PRV-ITEM [Previous Item], Key-PRVREC [Previous Record],
Key-SCRDOWN [Scroll Down], Key-SCRUP [Scroll Up],
Key-UP [Up],
Key-UPDREC (Equivalent to Record, Lock command on the default menu).
|
Note that you cannot redefine all Runform function keys with function key triggers. Specifically, you cannot ever redefine the following static function keys because they are often performed by the terminal or user interface management system and not by Oracle Forms.
[Clear Item], [Copy],
[Cut], [Delete Character],
[Delete Line], [Display Error],
[End of Line], [First Line],
[Insert Line], [Last Line],
[Left], [Paste],
[Refresh], [Right],
[Scroll Left], [Scroll Right],
[Search], [Select],
[Show Keys], [Toggle Insert/Replace],
[Transmit].
The default functionality performed by the following keys is not allowed in Enter Query mode:
[Clear Block], [Clear Form],
[Clear Record], [Accept],
[Insert Record], [Delete Record],
[Down], [Duplicate Item],
[Duplicate Record], [Block Menu],
[Next Block], [Next Primary Key],
[Next Record], [Next Set of Records],
[Previous Block], [Previous Record],
[Up], [Lock Record].
1.3.3.2 Key-Fn
A Key-Fn trigger fires when an operator presses the associated key. You can attach Key-Fn triggers to 10 keys or key sequences that normally do not perform any Oracle Forms operations. These keys are referred to as Key-F0 through Key-F9. Before you can attach key triggers to these keys, you or the DBA must use Oracle Terminal to map the keys to the appropriate functions.
1.3.3.3 Key-Others
A Key-Others trigger fires when an operator presses the associated key.
A Key-Others trigger is associated with all keys that can have key triggers associated with them but are not currently defined by function key triggers (at any level).
A Key-Others trigger overrides the default behavior of a Runform function key (unless one of the restrictions apply). When this occurs, however, Oracle Forms still displays the function key's default entry in the Show Keys screen.
1.3.4 Master-Detail Triggers
1.3.4.1 On-Check-Delete-Master
Oracle Forms creates this trigger automatically when you define a master-detail relation and set the Master Deletes property to Non-Isolated. It fires when there is an attempt to delete a record in the master block of a master-detail relation.
1.3.4.2 On-Clear-Details
Fires when a coordination-causing event occurs in a block that is a master block in a master-detail relation. A coordination-causing event is any event that makes a different record the current record in the master block.
1.3.4.3 On-Populate-Details
Oracle Forms creates this trigger automatically when you define a master-detail relation. It fires when Oracle Forms would normally need to populate the detail block in a master-detail relation.
1.3.5 Message-Handling Triggers
1.3.5.1 On-Error
An On-Error trigger fires whenever Oracle Forms would normally cause an error message to display.
1.3.5.2 On-Message
Fires whenever Oracle Forms would normally cause a message to display.
1.3.6 Navigational Triggers
1.3.6.1 Post-Block
Fires during the Leave the Block process.
1.3.6.2 Post-Form
Fires during the Leave the Form process, when a form is exited.
1.3.6.3 Post-Record
Fires during the Leave the Record process. Specifically, the Post-Record trigger fires whenever the operator or the application moves the input focus from one record to another. The Leave the Record process can occur as a result of numerous operations, including INSERT_RECORD,DELETE_RECORD, NEXT_RECORD, CREATE_RECORD, NEXT_BLOCK, PREVIOUS_BLOCK, etc.
1.3.6.4 Post-Text-Item
Fires during the Leave the Item process for a text item. Specifically, this trigger fires when the input focus moves from a text item to any other item.
1.3.6.5 Pre-Block
Fires during the Enter the Block process, during navigation from one block to another.
1.3.6.6 Pre-Form
Fires during the Enter the Form event, at form startup.
1.3.6.7 Pre-Record
Fires during the Enter the Record process, during navigation to a different record.
1.3.6.8 Pre-Text-Item
Fires during the Enter the Item process, during navigation from an item to a text item.
1.3.6.9 When-Form-Navigate
Fires whenever any peer form navigation takes place.
1.3.6.10 When-New-Block-Instance
Fires when the input focus moves to an item in a block that is different than the block that previously had input focus. Specifically, it fires after navigation to an item, when Oracle Forms is ready to accept input in a block that is different than the block that previously had input focus.
1.3.6.11 When-New-Form-Instance
At form start-up, Oracle Forms navigates to the first navigable item in the first navigable block. A When-New-Form-Instance trigger fires after the successful completion of any navigational triggers that fire during the initial navigation sequence. This trigger does not fire when control returns to a calling form from a called form. In a multiple-form application, this trigger does not fire when focus changes from one form to another.
1.3.6.12 When-New-Item-Instance
Fires when the input focus moves to an item. Specifically, it fires after navigation to an item, when Oracle Forms is ready to accept input in an item that is different than the item that previously had input focus.
1.3.6.13 When-New-Record-Instance
Fires when the input focus moves to an item in a record that is different than the record that previously had input focus. Specifically, it fires after navigation to an item in a record, when Oracle Forms is ready to accept input in a record that is different than the record that previously had input focus.
Fires whenever Oracle Forms instantiates a new record.
1.3.7 Query-Time Triggers
1.3.7.1 Post-Query
When a query is open in the block, the Post-Query trigger fires each time Oracle Forms fetches a record into a block. The trigger fires once for each record placed on the block's list of records.
1.3.7.2 Pre-Query
Fires during Execute Query or Count Query processing, just before Oracle Forms constructs and issues the SELECT statement to identify rows that match the query criteria.
1.3.8 Transactional Triggers
1.3.8.1 On-Check-Unique
During a commit operation, the On-Check-Unique trigger fires when Oracle Forms normally checks that primary key values are unique before inserting or updating a record in a base table. It fires once for each record that has been inserted or updated.
1.3.8.2 On-Close
Fires when an operator or the application causes a query to close. By default, Oracle Forms closes a query when all of the records identified by the query criteria have been fetched, or when the operator or the application aborts the query. The On-Close trigger augments the normal Oracle Forms "close cursor" phase of
1.3.8.3 On-Column-Security
Fires when Oracle Forms would normally enforce column-level security for each block that has the Column Security block property set On.
1.3.8.4 On-Commit
Fires whenever Oracle Forms would normally issue a database commit statement to finalize a transaction. By default, this operation occurs after all records that have been marked as updates, inserts, and deletes have been posted to the database.
1.3.8.5 On-Count
Fires when Oracle Forms would normally perform default Count Query processing to determine the number of rows in the database that match the current query criteria. When the On-Count trigger completes execution, Oracle Forms issues the standard query hits message: FRM-40355: Query will retrieve records.
1.3.8.6 On-Delete
Fires during the Post and Commit Transactions process. Specifically, it fires after the Pre-Delete trigger fires and before the Post-Delete trigger fires, replacing the actual database delete of a given row. The trigger fires once for each row that is marked for deletion from the database.
1.3.8.7 On-Fetch
When a query is first opened, fires immediately after the On-Select trigger fires, when the first records are fetched into the block. While the query remains open, fires again each time a set of rows must be fetched into the block.
1.3.8.8 On-Insert
Fires during the Post and Commit Transactions process. Specifically, it fires after the Pre-Insert trigger fires and before the Post-Insert trigger fires, when Oracle Forms would normally insert a record in the database. It fires once for each row that is marked for insertion into the database.
1.3.8.9 On-Lock
Fires whenever Oracle Forms would normally attempt to lock a row, such as when an operator presses a key to modify data in an item. The trigger fires between the keypress and the display of the modified data.
1.3.8.10 On-Logon
Fires once per logon when Oracle Forms normally initiates the logon sequence.
1.3.8.11 On-Logout
Fires when Oracle Forms normally initiates a logout procedure.
1.3.8.12 On-Rollback
Fires when Oracle Forms would normally issue a ROLLBACK statement, to roll back a transaction to the last savepoint that was issued.
1.3.8.13 On-Savepoint
Fires when Oracle Forms would normally issue a Savepoint statement. By default, Oracle Forms issues savepoints at form startup, and at the start of each Post and Commit Transaction process.
1.3.8.14 On-Select
Fires when Oracle Forms would normally execute the selection phase of a query, to identify the records in the database that match the current query criteria.
1.3.8.15 On-Sequence-Number
Fires when Oracle Forms would normally perform the default processing for generating sequence numbers for default item values.
1.3.8.16 On-Update
Fires during the Post and Commit Transactions process. Specifically, it fires after the Pre-Update trigger fires and before the Post-Update trigger fires, when Oracle Forms would normally update a record in the database. It fires once for each row that is marked for update in the form.
1.3.8.17 Post-Change
Fires when any of the following conditions exist:
• The Validate the Item process determines that an item is marked as Changed and is not NULL.
• An operator returns a value into an item by making a selection from a list of values, and the item is not NULL.
• Oracle Forms fetches a non-NULL value into an item. In this case, the When-Validate-Item trigger does not fire. If you want to circumvent this situation and effectively get rid of the Post-Change trigger, you must include a Post-Query trigger in addition to your When-Validate-Item trigger. See "Usage Notes" below.
1.3.8.18 Post-Database-Commit
Fires once during the Post and Commit Transactions process, after the database commit occurs. Note that the Post-Forms-Commit trigger fires after inserts, updates, and deletes have been posted to the database, but before the transaction has been finalized by issuing the Commit. The Post-Database-Commit Trigger fires after Oracle Forms issues the Commit to finalize the transaction.
1.3.8.19 Post-Delete
Fires during the Post and Commit Transactions process, after a row is deleted. It fires once for each row that is deleted from the database during the commit process.
1.3.8.20 Post-Forms-Commit
Fires once during the Post and Commit Transactions process. If there are records in the form that have been marked as inserts, updates, or deletes, the Post-Forms-Commit trigger fires after these changes have been written to the database but before Oracle Forms issues the database Commit to finalize the transaction.
If the operator or the application initiates a Commit when there are no records in the form have been marked as inserts, updates, or deletes, Oracle Forms fires the Post-Forms-Commit trigger immediately, without posting changes to the database.
1.3.8.21 Post-Insert
Fires during the Post and Commit Transactions process, just after a record is inserted. It fires once for each record that is inserted into the database during the commit process.
1.3.8.22 Post-Logon
Fires after either of the following events:
• The successful completion of Oracle Forms default logon processing.
• The successful execution of the On-Logon trigger.
1.3.8.23 Post-Logout
Fires after either of the following events:
• Oracle Forms successfully logs out of ORACLE.
• The successful execution of the On-Logout trigger.
1.3.8.24 Post-Select
The Post-Select trigger fires after the default selection phase of query processing, or after the successful execution of the On-Select trigger. It fires before any records are actually retrieved through fetch processing.
1.3.8.25 Post-Update
Fires during the Post and Commit Transactions process, after a row is updated. It fires once for each row that is updated in the database during the commit process.
1.3.8.26 Pre-Commit
Fires once during the Post and Commit Transactions process, before Oracle Forms processes any records to change. Specifically, it fires after Oracle Forms determines that there are inserts, updates, or deletes in the form to post or commit. The trigger does not fire when there is an attempt to commit, but validation determines that there are no changed records in the form.
1.3.8.27 Pre-Delete
Fires during the Post and Commit Transactions process, before a row is deleted. It fires once for each record that is marked for delete. Note: Oracle Forms creates a Pre-Delete trigger automatically for any master-detail relation that has the Master Deletes property set to Cascading.
Pre-Insert Fires during the Post and Commit Transactions process, before a row is inserted. It fires once for each record that is marked for insert.
1.3.8.28 Pre-Logon
Fires just before Oracle Forms initiates a logon procedure to the data source.
1.3.8.29 Pre-Logout
Fires once before Oracle Forms initiates a logout procedure.
1.3.8.30 Pre-Select
Fires during Execute Query and Count Query processing, after Oracle Forms constructs the SELECT statement to be issued, but before the statement is actually issued. Note that the SELECT statement can be examined in a Pre-Select trigger by reading the value of the system variable SYSTEM.LAST_QUERY.
1.3.8.31 Pre-Update
Fires during the Post and Commit Transactions process, before a row is updated. It fires once for each record that is marked for update.
1.3.9 Validation Triggers
1.3.9.1 When-Validate-Item
Fires during the Validate the Item process. Specifically, it fires as the last part of item validation for items with the New or Changed validation status.
1.3.9.2 When-Validate-Record
Fires during the Validate the Record process. Specifically, it fires as the last part of record validation for records with the New or Changed validation status.
1.3.9.3 User-Named Trigger
A user-named trigger is a trigger that you define yourself in a form, and then call explicitly from other triggers or user-named subprograms. Each user-named trigger defined at the same definition level must have a unique name.
To execute a user-named trigger, you must call the EXECUTE_TRIGGER built-in procedure, as shown here:
Execute_Trigger('my_user_named_trigger');
Note: You can write user-named PL/SQL subprograms to perform almost any task for which you might use a user-named trigger.
FORM TRIGGERS PART 2
============
Source :-> http://kashif-oracle-forms.blogspot.com/2009/07/types-of-triggers-in-forms.html
Types of Triggers in Forms
1. Types of Triggers in Forms
Block-processing triggers: - Block processing triggers fire in response to events related to record management in a block. E.g. When-Create-Record, When-Clear-Block, When-Database-Record, When-Remove-Record
Interface event triggers: - Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control. E.g. When-Button-Pressed, When-Checkbox-Changed, Key- [all], When-Radio-Changed, When-Timer-Expired, When-Window-Activated, When-Window-Resized
Master-detail triggers: - Form Builder generates master-detail triggers automatically when you define a master-detail relation between blocks. The default master-detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless you are developing your own custom block-coordination scheme, you do not need to define these triggers yourself. Instead, simply create a relation object, and let Form Builder generate the triggers required to manage coordination between the master and detail blocks in the relation. E.g. On-Check-Delete-Master, On-Clear-Details, On-Populate-Details
Message-handling triggers: - Form Builder automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events. E.g. On-Error, On-Message
Navigational triggers: - Navigational triggers fire in response to navigational events. For instance, when the operator clicks on a text item in another block, navigational events occur as Form Builder moves the input focus from the current item to the target item. Navigational events occur at different levels of the Form Builder object hierarchy (Form, Block, Record, Item). Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- triggers fire as Form Builder navigates internally through different levels of the object hierarchy. As you might expect, these triggers fire in response to navigation initiated by an operator, such as pressing the [Next Item] key. However, be aware that these triggers also fire in response to internal navigation that Form Builder performs during default processing. To avoid unexpected results, you must consider such internal navigation when you use these triggers. E.g. Pre-Form, Pre-Block, Pre-Text-Item, Post-Text-Item, Post-Record, Post-Block, Post-Form
When-New-Instance triggers fire at the end of a navigational sequence that places the input focus in a different item. Specifically, these triggers fire just after Form Builder moves the input focus to a different item, when the form returns to a quiet state to wait for operator input. Unlike the Pre- and Post- navigational triggers, the When-New-Instance triggers do not fire in response to internal navigational events that occur during default form processing. E.g. When-New-Form-Instance, When-New-Block-Instance, When-New-Record-Instance, When-New-Item-Instance
Query-time triggers: - Query-time triggers fire just before and just after the operator or the application executes a query in a block. E.g. Pre-Query, Post-Query
Transactional triggers: - Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source. E.g. On-Delete, On-Insert, On-Lock, On-Logon, On-Update, Post-Database-Commit, Post-Delete, Post-Forms-Commit, Post-Insert, Post-Update, Pre-Commit, Pre-Delete, Pre-Insert, Pre-Update
Validation triggers: - Validation triggers fire when Form Builder validates data in an item or record. Form Builder performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation. E.g. When-Validate-Item, When-Validate-Record
2. Sequence of Trigger Fire while Committing
Ø KEY Commit
Ø Pre Commit
Ø Pre/On/Post Delete
Ø Pre/On/Post Update
Ø Pre/On/Post Insert
Ø On commit
Ø Post Database Commit
3. Master-Detail Relation (Triggers/Procedures/Properties)
On-Check-Delete-Master: - Fires when Form Builder attempts to delete a record
in a block that is a master block in a master-detail relation.
On-Clear-Details: - Fires when Form Builder needs to clear records in a block
that is a detail block in a master-detail relation because those records no longer
correspond to the current record in the master block.
On-Populate-Details: - Fires when Form Builder needs to fetch records into a block that is the detail block in a master-detail relation so that detail records are synchronized with the current record in the master block.
(i) Isolated: - Masters Can be deleted when Child is existing
Triggers: - On Populate details Block
On Clear Details Form
Procedure
Check Package Failure
Clear all master Detail
Query Master Detail
(ii) Non- Isolated: - Masters Cannot be deleted when Child is existing.
Triggers: - On Populate details Block
On Check Delete master Block
On Clear Details Form
Procedure
Check Package Failure
Clear all master Detail
Query Master Detail
(iii) Cascading: - Child Record Automatically Deleted when Masters is deleted.
Triggers: - On Populate details Block
Pre Delete Block
On Clear Details Form
Procedure
Check Package Failure
Clear all master Detail
Query Master Detail
4. Dynamically create LOV/List Item
You can also add list elements individually at runtime by using the ADD_LIST_ELEMENT built-in subprogram, or you can populate the list from a record group at runtime using the POPULATE_LIST built-in. If you populate the list from a record group, be sure that the record group you are using to populate the list contains the relevant values before you call POPULATE_LIST. If the record group is a static record group, it will already contain the appropriate values. Otherwise, you should populate the group at runtime using one of the record group subprograms.
5. Object Libraries (Use/Benefits)
The Object Library provides an easy method of reusing objects and enforcing standards across the entire development organization.
Object Library can be used to:
1. Create, store, maintain, and distribute standard and reusable objects.
2. Rapidly create applications by dragging and dropping predefined objects to your form.
There are several advantages to using object libraries to develop applications:
1. Object libraries are automatically re-opened when you startup Form Builder, making your reusable objects immediately accessible.
2. You can associate multiple object libraries with an application. For example, you can create an object library specifically for corporate standards, and you can create an object library to satisfy project-specific requirements.
3. Object libraries feature Smart Classes-- objects that you define as being the standard. You use Smart Classes to convert objects to standard objects.
6. Key-next/Post-Text (Difference)
Post-Text–Item: Fires during the Leave the Item process for a text item. Specifically, this trigger fires when the input focus moves from a text item to any other item.
Key-Next-Item: The key-next is fired as a result of the key action. Key next will not fire unless there is a key event.
7. Call From/New Form/Open Form (Difference)
Call Form: Runs an indicated form while keeping the parent form active. Form Builder runs the called form with the same Runform preferences as the parent form. When the called form is exited Form Builder processing resumes in the calling form at the point from which you initiated the call to CALL_FORM.
PROCEDURE CALL_FORM (formmodule_name VARCHAR2, display NUMBER, switch_menu NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name/id VARCHAR2);
New Form: Exits the current form and enters the indicated form. The calling form is terminated as the parent form. If the calling form had been called by a higher form, Form Builder keeps the higher call active and treats it as a call to the new form. Form Builder releases memory (such as database cursors) that the terminated form was using.
Form Builder runs the new form with the same Runform options as the parent form. If the parent form was a called form, Form Builder runs the new form with the same options as the parent form.
PROCEDURE NEW_FORM (formmodule_name VARCHAR2, rollback_mode NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name/id VARCHAR2);
Open Form: Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.
PROCEDURE OPEN_FORM (form_name VARCHAR2, activate_mode NUMBER, session_mode NUMBER, data_mode NUMBER, paramlist_id/name PARAMLIST);
8. Types of Canvases (Stacked/Content Difference)
(i) Content Canvas (Default Canvas) [A content canvas is the required on each window you create]
(ii) Stack Canvas [you can display more then one stack canvas in a window at the same time]
(iii) Tab Type Window [In Tab canvas that have tab pages and have one or more then tab page]
(iv) Toolbar Canvas [A toolbar canvas often is used to create Toolbar Windows. There are two type of Toolbar window.
a. Horizontal Toolbar Canvas: - Horizontal Toolbar canvases are displayed at the top of the window, Just Under the Main Menu Bar.
b. Vertical Toolbar Canvas: - While vertical Toolbar are displayed along the Left Edge of the window.
9. Object Groups (Use)
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module.
Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects. For example, you might build an appointment scheduler in a form and then decide to make it available from other forms in your applications. The scheduler would probably be built from several types of objects, including a window and canvas, blocks, and items that display dates and appointments, and triggers that contain the logic for scheduling and other functionality. If you packaged these objects into an object group, you could then copy them to any number of other forms in one simple operation.
You can create object groups in form and menu modules. Once you create an object group, you can add and remove objects to it as desired.
10. Various Block Co-ordination Properties
The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deferred with No Auto Query
The operator must navigate to the detail block and explicitly execute a query
11. How to attach same LOV to multiple items
We can use the same LOV for 2 columns by passing the return values in global values and using the global values in the code.
12. Report Level Triggers (Sequence)
· Before parameter form
· After parameter form
· Before Report
· Between Pages
· After Report
13. Static & Dynamic LOV
The static LOV contains the predetermined values while the dynamic LOV contains values that come at run time
14. Format Triggers (What are they)
A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object.
15. Flex & Confine Mode in Reports
Confine mode:
a. Switched on by default; change via View® View Options® Layout...
b. It prevents operations which would cause a report not to work e.g. moving a field outside its parent repeating frame
Flex mode:
Moves the object it’s enclosing objects and objects in their push path simultaneously to maintain the same overall relationship in the report. E.g. if you try to move a field outside its repeating frame, the Repeating Frame will grow to accommodate the field and so will any objects around the repeating frame.
Only one object can be moved/resized at one time in flex mode - if you try more than one only one whose control point is clicked on will be done, the other objects will be de-selected.
Objects can be moved/resized horizontally or vertically; not diagonally.
16. Matrix Reports (Matrix By Groups)
A matrix (cross tab) report contains one row of labels, one column of labels, and information in a grid format that is related to the row and column labels. A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database.
To create a matrix report, you need at least four groups: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells. The groups can belong to a single query or to multiple queries.
A matrix with group report is a group above report with a separate matrix for each value of the master group.
A nested matrix (crosstab) report is a matrix report in which at least one parent/child relationship appears within the matrix grid.
The new Child Dimension property of the nested group enables you to eliminate empty rows and/or columns in your single-query nested matrix.
Types of Matrix Reports
Simple Matrix Report:
Is a matrix with only two dimensions
Nested Matrix Report: Has multiple dimensions going across and/or down the page
Multi-Query Matrix with Break: Is similar to a nested matrix report in that it has more than two dimensions. Does not display records that do not contain data
Matrix Break Reports: Contains a new matrix for each master record
17. Lexical & Bind Parameters in Reports
Lexical Parameters: Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL. Look at the example below.
You create a lexical reference by entering an ampersand (&) followed immediately by the column or parameter name. A default definition is not provided for lexical references. Therefore, you must do the following:
Ø Before you create your query, define a column or parameter in the data model for each lexical reference in the query. For columns, you must enter Value if Null, and, for parameters, you must enter Initial Value. Report Builder uses these values to validate a query with a lexical reference.
Ø Create your query containing lexical references.
Bind Parameters: Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.
You create a bind reference by entering a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.
18. Column Mode Property in Reports
The Column Mode property controls how Report Builder fetches and formats data for instances of repeating frames. With Column Mode set to Yes, the next instance of a repeating frame can begin formatting before the previous instance is completed. With Column Mode set to No, the next instance cannot begin formatting before the previous instance is completed. Column Mode is used mainly for master repeating frames or repeating frames that contain fields that may expand vertically or horizontally (i.e., elasticity is Variable or Expand).
19. Diff b/w Package Spec & Body
Packages provide a method of encapsulating and storing related procedures, funtions and other package constructs as a unit in the database. They offer increased functionality (for example, global package variables can be declared and used by any procedure in the package). They also improve performance (for example, all objects of the package are parsed, compiled, and loaded into memory once).
Package specification contains declarations of public constructs where as the package body contains definitions of all those public constructs and declarations & definitions of private constructs.
20. P/L SQL Tables / Arrays
PL/SQL tables are declared in the declaration portion of the block. A table is a composite datatype in PL/SQL. PL/SQL tables can have one column and a primary key neither of which can be named. The column can be any scalar type but primary key should be a BINARY_INTEGER datatype.
Rules for PL/SQL Tables:
1. A loop must be used to insert values into a PL/SQL Table
2. You cannot use the Delete command to delete the contents of PL/SQL Table. You must assign an empty table to the PL/SQL table being deleted.
21. Various Cursor Attributes
SQL%ROWCOUNT: Number of rows affected by most recent SQL statement.
SQL%FOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement affects one or more rows.
SQL%NOTFOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement does not affect any row.
SQL%ISOPEN: Always evaluates to FALSE because P/L SQL closes implicit cursors immediately after they are executed.
22. Different Database Triggers
Database triggers are PL/SQL, Java, or C procedures that run implicitly whenever a table or view is modified or when some user actions or database system actions occur. Database triggers can be used in a variety of ways for managing your database. For example, they can automate data generation, audit data modifications, enforce complex integrity constraints, and customize complex security authorizations.
Row Triggers
A row trigger is fired each time the table is affected by the triggering statement.
For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement.
If a triggering statement affects no rows, a row trigger is not executed at all.
Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected.
Statement Triggers
A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected).
For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.
Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.
For example, if a trigger makes a complex security check on the current time or user, or if a trigger generates a single audit record based on the type of triggering statement, a statement trigger is used.
BEFORE vs. AFTER Triggers
When defining a trigger, you can specify the trigger timing.
That is, you can specify whether the trigger action is to be executed before or after the triggering statement.
BEFORE and AFTER apply to both statement and row triggers.
BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. This type of trigger is commonly used in the following situations:
BEFORE triggers are used when the trigger action should determine whether the triggering statement should be allowed to complete. By using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.
BEFORE triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement.
AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed. AFTER triggers are used in the following situations:
AFTER triggers are used when you want the triggering statement to complete before executing the trigger action.
If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering statement.
Combinations
Using the options listed in the previous two sections, you can create four types of triggers:
BEFORE statement trigger Before executing the triggering statement, the trigger action is executed.
BEFORE row trigger Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is executed provided that the trigger restriction was not violated.
AFTER statement trigger After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.
AFTER row trigger After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is executed for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.
New Database Triggers
Startup, Shutdown, Logon, Logoff, Alter, Create, Drop
23. List Item Types
Poplist: The poplist style list item appears initially as a single field (similar to a text item field). When the end user selects the list icon, a list of available choices appears.
Tlist: The Tlist style list item appears as a rectangular box, which displays a fixed number of values. When the Tlist contains values that cannot be displayed (due to the displayable area of the item), a vertical scroll bar appears, allowing the end user to view and select undisplayed values.
Combo Box: The combo box style list item combines the features found in poplists and text items. It displays fixed values and can accept a user-entered value.
