Data Transformation Engine Database Interface Designer Reference Guide

Chapter 9 - Database Triggers Specifying a Combination of Different Event Classes
Database Interface Designer Reference Guide
142
Format of the When Expression
There are two basic formats that can be used for the When expression as specified
in the Trigger Specification dialog box:
♦ clauses referencing table columns
♦ clauses using the SELECT 1 FROM format to establish conditions of execution
Table Column Format
The When expression in the Trigger Specification dialog box can contain any
SQL expressions that are valid for the database. If database columns are
referenced in the expression, the column name must be qualified with the
tablename and the tablename.column_name enclosed in square brackets ([ ]).
For example, if a map should be triggered only when there is a row in the
MyTable table having a column entitled Status with a value of Ready, specify the
Insert into, Delete from, and Update of events and enter the following
expression in the When field:
[MyTable.Status] = 'Ready'
SELECT 1 FROM Format
The When expression can support any valid SQL statement that begins with a
SELECT 1 FROM clause. After a database event is detected on the DBMS, the entire
statement (as it appears in the Trigger Specification dialog box) is executed.
For example, if a database input card should be triggered for execution only during
a certain time of day, the following statement could be entered:
Note This is example only applies to a DBMS of Oracle8 or above.
select 1 from dual where TO_CHAR(SYSDATE, 'HH24') > '00' AND
TO_CHAR(SYSDATE, 'HH24') < '06'
This When clause would restrict processing of any database "watch" events to the
timeframe of between midnight and 6AM.