Data Transformation Engine Database Interface Designer Reference Guide

Chapter 9 - Database Triggers Database Triggers Overview
Database Interface Designer Reference Guide
135
Table-based Triggering
A typical usage for table-based triggering would be to set up a trigger that will
execute a map to run after another map has completed processing and has
inserted rows into a table. For example:
Map 1
SELECT Name
FROM My_Table
My_Table
Map 2
Trigger
Database
In this example, Map 2 is triggered as soon as the rows inserted into My_Table
become known to another process. This occurs when the transaction is committed,
which is when Map 1 completes.
If other settings are used to specify when the transaction is committed, such as
the Card value for the Source > Transaction > Scope setting or by specifying
the Commit by Statement adapter command (
-CSTMT) in the GET > Source >
Command setting, the database transaction will complete prior to completion of
the map. For information about the Source > Transaction > Scope setting, refer
to Chapter 5 - Database Sources and Targets. For information about using the
Commit by Statement adapter command (
-CSTMT), refer to the Resource Adapters
Reference Guide.
Row-based Triggering
The row-based triggering design enables a fault-tolerant, multi-server-aware
triggering mechanism that can enable maps to selectively process those rows
associated with a triggered watch event. In the preceding sentence, fault-tolerant
means that data is not lost if a process fails, a connection is dropped, an Event
Server stops, or some other unforeseen interruption occurs. Multi-server-aware
means that Event Servers on different machines can connect to and "watch" the
same table.
The main difference between row-based and table-based triggering is that with
row-based triggering, the query executes on only those rows that have been
updated or inserted, not on the entire table. Additionally, batch-like processing is
possible if the When clause is used to control the time and/or date at/upon which
processing should occur.