How to Add an Oracle Trigger Task Step

The Oracle Trigger The Oracle Trigger tool uses the Oracle Server trigger functionality to create a trigger on a table of a specified Oracle data source. When a specified data modification is attempted, such as, an attempt to add or delete a table row, the trigger then causes a task to run. step triggers a task to run when a specific table in an Oracle database is updated, deleted, or has a new row inserted.

In addition, global variables Variables are created and used extensively throughout and can either have fixed values or values that are dynamically populated when a task is run. Global variables are available for all tasks in this BPA Platform installation. Once created, global variables can be used by any task. created for the task can be mapped to a specific column in the selected table, so that the column value may be used in other steps.

Prerequisites

Adding an Oracle Trigger Agent

The Oracle Trigger Agent is a service that sits between BPA Platform and an Oracle server. It can be installed onto any Oracle Server. It is responsible for both maintaining the triggers in Oracle Server, and for handling the running of a task once a trigger has been fired.

When an Oracle trigger is fired, a connection is made to the agent service which then launches the applicable task.

The Oracle Trigger Agent must be installed on the computer hosting the Oracle database instance, on a Windows Server operating system.

For a detailed description of how to install the Agent, see How to Install the Oracle Trigger Agent.

(Optional) Polling the Oracle Database

The Oracle Trigger Agent constantly polls its monitored database for any changes. In high-traffic databases where inserts, updates, and deletes happen frequently, this may not be suitable. Do this in the Oracle Global Configuration interface.

You open this interface from the resources tree — expand System > Tools > Event and double-click Oracle Trigger in the items list.

Adding a Task Step

To add a new Oracle Trigger step to an existing task:

Open the relevant task (if creating a new task, see How to Create a Basic Task).
Either:

►

Click and drag the Oracle Trigger icon from the Task Browser to the task Design area.

–OR–

►

From the task's Design tab, right-click on empty space and select New > Event > Oracle Trigger.

In the General tab, Name your task step.

Go to the Main tab and enter the database details for the Oracle Trigger Agent to monitor.

Go to the Trigger tab and configure the database operations that trigger this task to run.

How Do I Do This?

You use the Trigger tab to configure the trigger that activates this task. You can choose to use the provided options to create the trigger or manually write the trigger script.

Click the image to view full size.

►

Trigger Type — You can create triggers for events that occur in the nominated database table:

■

UPDATE — Trigger the task to run when a specified column is updated in the database

■

INSERT — Trigger the task to run when a new record is added to the database table

■

DELETE — Trigger the task to run when an existing record is deleted from the database table

►

Fire Type — You can choose when this trigger is fired when combined with the Trigger Type:

■

BEFORE — "Before" triggers fire before the table has been updated or a record inserted or deleted

■

AFTER — "After" triggers wait for the event (update, insert, delete) to finish before processing the trigger

If you are creating a Trigger Type of UPDATE, you can also Only fire trigger when a selected Column is updated. To do so, enable this parameter and select the relevant column(s) from the table underneath.

A monitored event may result in multiple records being updated, inserted, or deleted, for example, importing a CSV file containing new customer records. For such cases, you can choose how often the task is triggered to run:

►

Run Task once for each Trigger — The task is run once for each Trigger Type fired, that is, numerous updates, inserts, or deletes can be made to the table but the trigger only fires once.

►

Run Task once for each Row in Results Set in — When the specified trigger is witnessed, each affected record is written to an Inserted (UPDATE and INSERT trigger types) or Deleted (DELETE only), and the task fired once. Each table is created by the Oracle Trigger tool and held in memory only.

For either of the above tables, you can then Map Columns to Task Parameters for use in later task steps. All available task variables Variables are created and used extensively throughout and can either have fixed values or values that are dynamically populated when a task is run. Task variables are only available for the specific task they are created for. Users with Task Administrator rights can create task variables. are displayed automatically in the table. For those task variables required by this task step, map them to the relevant the database Columns. Leave those task variables that are not required by this task step as (unmapped).

You can further narrow down the event that triggers this task by adding a Filter. Enable Filter Table and click Filter:

Oracle Trigger - Trigger Tab - Table Filter Expression

Drag-and-drop any Column to the Logical Expression area to build your expression.

Note: At the time of writing, the use of wildcards in the filter expression is not supported. Nor can you make use of task or global variables here.

For example, in a Sales Orders database, a report needs generating every time an order over a set value is received. The following expression is therefore created for the trigger Inserted.ITEMS_NET > = 5000.00 to cover occurrences where the value is equal to or exceeds £5000.00.

For more information, go to the main Trigger tab topic.

Click to save the task step.