5 Adding Event Triggers

BI Publisher data models support before data and after data event triggers and schedule triggers. This chapter describes how to define triggers in your data model.

This chapter includes the following sections:

5.1 About Triggers

An event trigger checks for an event and when the event occurs, it runs the code associated with the trigger. BI publisher supports the following events: before data set is executed, after data set is executed and before a scheduled job is about to execute. Correspondingly, there are three types of event triggers:

  • Before Data — fires right before the data set is executed.

  • After Data — fires right after the data engine executes all data sets and generates the XML.

  • Schedule Trigger - fires when a scheduled job is triggered and before it runs.

Before data and after data triggers execute a PL/SQL function stored in a PL/SQL package in your Oracle Database. The return data type for a PL/SQL function inside the package must be a Boolean type and the function must explicitly return TRUE or FALSE.

A schedule trigger is associated with a scheduled job. It is a SQL query that executes at the time a report job is scheduled to run. If the SQL returns any data, the report job runs. If the SQL query returns no data, the job instance is skipped.

Note that event triggers are not used to populate data used by the bursting definition. See Adding Bursting Definitions.

5.2 Adding Before Data and After Data Triggers

To add before data or after data event triggers:

  1. On the data model Properties pane, enter the Oracle DB Default Package that contains the PL/SQL function signature to execute when the trigger fires. See Section 1.7, "Setting Data Model Properties."

  2. From the task pane, click Event Triggers.

  3. From the Event Triggers pane, click Create New Event Trigger.

  4. Enter the following for the trigger:

    • Name

    • Type — Select Before Data or After Data.

    • Language — Select PL/SQL.

      The lower pane displays the available functions in the Oracle DB Default Package that you entered in the data model Properties in Step 1.

      Figure 5-1 shows an event trigger.

      Figure 5-1 Event Trigger

      Event trigger
  5. Select the package from the Available Functions box and click the arrow to move a function to the Event Trigger box. The name appears as PL/SQL <package name>.<function name>.

    Important:

    If you define a default package then you must define all parameters as a global PL/SQL variable in the PL/SQL package. You can then explicitly pass parameters to your PL/SQL function trigger or all parameters are available as a global PL/SQL variable.

5.2.1 Order of Execution

If you define multiple triggers of the same type, they fire in the order that they appear in the table (from top to bottom).

To change the order of execution:

  1. Use the Reorder arrows to place the triggers in the correct order.

5.3 Creating Schedule Triggers

A schedule trigger fires when a report job is scheduled to run. Schedule triggers are of type SQL Query. When a report job is scheduled to run, the schedule trigger executes the SQL statement defined for the trigger. If data is returned, then the report job is submitted. If data is not returned from the trigger SQL query, the report job is skipped.

The schedule trigger that you associate with a report job can reside in any data model in the catalog. You do not need to create the schedule trigger in the data model of the report for which you which to execute it. You can therefore reuse schedule triggers across multiple report jobs.

To add a Schedule Trigger:

  1. In the data model editor task pane, click Event Triggers.

  2. From the Event Triggers pane, click the Create New icon.

  3. Enter the following for the trigger:

    • Name - enter a name for the trigger.

    • Type — select Schedule.

    • Language — defaults to SQL Query.

  4. In the lower pane, enter the following:

    • Options - select this box to cache the results of the trigger query.

    • Data Source - select the data source for the trigger query.

    • SQL Query - enter the query in the text area, or click Query Builder to use the utility to construct the SQL query. For information, see Section 2.3.3, "Using the SQL Query Builder."

      You can include parameters in the trigger query. Define the parameter in the same data model as the trigger. Enter parameter values when you schedule the report job.

    It the SQL query returns any results, the scheduled report job executes. Figure 5-2 shows a schedule trigger to test for inventory levels based on a parameter value that can be entered at runtime.

    Figure 5-2 Schedule Trigger

    Surrounding text describes Figure 5-2 .

    For information on implementing the schedule trigger in the report job, see "Defining the Schedule for a Job" in the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Publisher.