Trigger (-TR or -TRIG)

This command is available only for the Oracle and Microsoft™ SQL Server database adapters.

Use the Trigger adapter command (-TRIG) for a source to specify the trigger string for a trigger specification. For more information about specifying database triggers, refer to the Database Interface Designer documentation.

The Trigger adapter command (-TRIG) can be used as a trigger specification for a query that has no defined trigger in the Database Interface Designer. It can also be used to override conditions that are already defined in a trigger specification associated with a query.

The pipe character (|) is used to represent OR and the ampersand character (&) is used to represent AND. The combination of characters &| represents an AND/OR statement. The value inserted as the when_clause must be a valid SQL syntax with table.column_name enclosed in square brackets.


-TRIG {[R] [I tablename1 [{&||} tablename2]...]
   [D tablename1 [{&||} tablename2]...
   [U tablename1 [{&||} tablename2]...]}
   [W when_clause [[table.column_name1]]...]
Value
Description
R
This indicates that row-based triggering is being used as opposed to table-based.
I tablename1 [{&||}tablename2]...
The insertion of rows into the specified table(s) serves as an input event trigger to the map using this query as a source.
D tablename1 [{&||}tablename2]...
The deletion of rows from the specified table(s) serves as an input event trigger to the map using this query as a source.
U tablename1 [{&||}tablename2]...
Updating values in the specified column(s) serves as an input event trigger to the map using this query as a source.
W when_clause [[table.column_name1]...]
This is an expression that will be evaluated when the other events have occurred. If this expression evaluates to TRUE, the conditions of the trigger specification are met and the map is launched. If the expression is not TRUE, the state is restored as it was prior to any events occurring.

This expression may contain any SQL expressions that are valid for the target database. If database columns are referenced, the column name must be qualified with the table name and enclosed in square brackets.

The following is an example of specifying trigger events and conditions using the adapter:


-TR I ACCT_INFO | TRANSACTIONS D ACCT_INFO U ACCT_INFO
W [TRANSACTION.TRANS_TYPE]='PY'

This example causes the following to occur:

  • Launch the map that uses this query as a data source when one of the following conditions is met:
    • there is insertion into the ACCT_INFO TRANSACTIONS tables
    • there is a deletion from the ACCT_INFO table
    • ACCT_INFO is updated,

but only when a row exists in TRANSACTION with the column TRANS_TYPE set to 'PY'.

You cannot combine both | and & in an event class (Insert into, Delete from, or Update of) if more than two tables or columns are being combined. For example, the following command is invalid:


-TR I TABLE1 & TABLE2 | TABLE3