Firing sequence of report triggers is one of the most frequently asked questions in interviews. Before we start with the sequence of trigger firing, let us have brief understanding about them.
There are totally eight report triggers in Oracle Reports. The classification of those triggers can be like this,
Report level triggers with sequence in which they fire
Before Parameter Form
After Parameter Form
Report Triggers Explained
Before Parameter Form Trigger
This trigger fires before the Parameter Form is displayed at run-time. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns.
Basically to validate parameters of parameter form this trigger is used.
After Parameter Form Trigger
This trigger fires after Parameter Form is displayed at run-time. From this trigger, you can access parameters, check their values and change parameter values. If an error occurs, you can return to the Runtime Parameter Form with this trigger. Columns from the data model are not accessible from this trigger.
After Parameter form is also used to validate command line parameters.
Before Report Trigger
This trigger fires before the report is executed but after queries is parsed and data is fetched.
Between Pages Trigger
This trigger fires before each page of the report is formatted except the first page. This trigger can be used for customized page formatting. In paper design view, this trigger fires only for the first time. If you return back to that page it will not fire.
After Report Trigger
This trigger fires after you exit Paper Design view, or after report output is sent to a specified destination, such as a file, a printer. This trigger can be used to flush any initial processing that was done, such as deleting tables and reset the values. Even if the report will end in error, after report trigger will fire.
Validation triggers are PL/SQL functions executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. They can also be used to validate the Initial Value property of the parameter. The function must return a Boolean value (TRUE or FALSE). If Boolean value is TRUE or FALSE, user is returned to Runtime Parameter Form depending on what function returns.
Example: Prevent report output to be anything else other than printer
Format triggers are PL/SQL functions fired before the object is displayed. It dynamically modifies the formatting attributes of the object. It must return a Boolean value (TRUE or FALSE) depending on which current instance of an object is included or excluded from report output.
Example: Highlighting certain values in report
Action Triggers are PL/SQL functions executed when a button is selected in the Paper View design. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.
Example: Dynamically call another report by clicking on button in Paper View design.
Order of Reports Triggers Execution in Detail
- Before Parameter Form trigger is fired.
- Runtime Parameter Form appears (if not suppressed).
- After Parameter Form trigger is fired (unless the user cancels from the Runtime Parameter Form).
- Report is “compiled”.
- Queries are parsed.
- Before Report trigger is fired.
- SET TRANSACTION READONLY is executed (if specified with the READONLY command line keyword or setting).
- The report is run and the Between Pages trigger fires for each page except the first one. (Note that data can be fetched at any time while the report is being formatted.) COMMITs can occur during this time due to: SRW.DO_SQL with DDL, or if ONFAILURE=COMMIT, and the report fails.
- COMMIT is executed (if READONLY is specified) to end the transaction.
- After Report trigger is fired.
Hope you like the article and find it useful. We highly appreciate comments and feedback.