Oracle Trigger Tool

What is the Oracle Trigger Tool?

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.

If a Database Query (ODBC) step is used in the task to retrieve a recordset from the Oracle data source, then the memory feature can be used in a subsequent step to memorise all of the data into a Repository.

Oracle Trigger Tool Features

• A trigger is activated when any row is updated, deleted or a new row inserted
• A trigger is activated when specified columns are updated
• Values of updated, deleted or new rows mapped to variables for use in other task steps

White Paper - Oracle Trigger

The Oracle Trigger white paper introduces the tool.

Download White Paper

Oracle Trigger Tool Technical Summary

Working with Other Tools

The Oracle Trigger tool is independent of any other steps used in a task. It does not directly consume objects outputted by other task steps, nor does it expose objects that can be directly consumed by other task steps. However, Oracle Trigger data is mapped to task variables which can be used by subsequent task steps.

Dependencies

The Oracle Trigger tool requires the following products, technologies, protocols, or systems:

• Oracle 8, 9 or 10 Data Source — All versions support the use of triggers on the following platforms:
  • Linux — Linux kernels; 2.2, 2.4 and 2.6
  • Linux — Debian, Red Hat and other distributions
  • AIX
  • FreeBSD
  • HP — HP-UX (32- and 64-bit versions)
  • SGI Irix
  • DEC OSF1
  • Apple Mac OS X
• Oracle Trigger Agent — The agent must be installed on the Oracle Server

The Oracle Trigger Agent requires Java runtime version 1.6 or higher, 32-bit edition, installed as a minimum. Note the following:

• If Java is not detected on the Oracle server, the installation aborts and you are prompted to install 32-bit Java runtime
• If only 64-bit Java runtime is detected, the installation aborts and you are prompted to install the 32-bit version
• If both 32- and 64-bit Java runtimes are detected, the installation continues

For more information, see About the Oracle Trigger Agent below.

About the 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

Global Configuration

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. You can adjust the polling interval here.

You open the Oracle Global Configuration dialog box by either:

• Going to Manage > Tools > Event > Oracle Trigger
• From the Task Manager, expanding BPA Platform > System > Tools > Event and double-clicking Oracle Trigger in the Items List
  

From here, change the following:

• Oracle Agent — A list of all agents that are registered with the BPA Platform server
• Agent Synch Polling Interval — The number of seconds for how frequently the agent checks the monitored database for changes

Step Configuration

The Oracle Trigger 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 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.

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

• Click and drag the Oracle Trigger icon from the Task Browser to the task Design area.
• From the task’s Design tab, right-click on empty space and select Add > Event > Oracle Trigger.

For a detailed description of how to create new tasks, refer to the product help.

About the General Tab

The General tab is used to enter the following details for the step:

• Name — Enter a meaningful name for the step
  TIP: If this task instance makes use of two or more Oracle Trigger steps, ensure the Name used is unique foreach individual step.
• Description — If required, enter a description of this step

About the Main Tab

You use the Main tab to configure the trigger that activates this task:

Configure the following:

• Server — Configure the server hosting the Oracle Trigger Agent’s details:
  • Select Server — Choose the relevant Oracle Trigger Agent and database instance
  • Oracle SID — The Oracle server ID
  • Advanced — Click to enter the TCP port number used to communicate with the Oracle database instance; this defaults to port 1521
• Credentials — Provide the credentials used to log into the Oracle instance:
  • Login Name
  • Password
    NOTE: This user must have the correct permissions to establish the connection between the Oracle instance and the Oracle Trigger Agent when triggers are fired. They must also have permission to execute the utl_tcp package.
  • Connect to Agent on port — The TCP port number used to communicate with the Oracle Trigger Agent; this defaults to port 1521
• Select Table — Select the Oracle table for the Oracle Trigger Agent to monitor for this task; click Browse to select the table. If you cannot click Browse, a connection to the Agent, the Oracle instance, or both cannot be made.
• Queue Trigger events if Server or Agent are unavailable to run Tasks — Enable this parameter to cache the trigger events until BPA Platform or the Agent are available again.

About the Trigger Tab

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.

Configuring the Trigger

Configure your trigger by selecting the following:

• Trigger Type — You can create triggers for events that occur on 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.

Running Triggers and Tasks

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 table (UPDATE and INSERT trigger types) or Deleted table (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 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).

Adding a Filter Expression

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

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

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.

Scripting the Trigger

If required, you can use the Script tab to either manually create the trigger or edit the trigger created in the Configure tab.

Click Edit to write or edit the Oracle script manually.