Format of the when expression
There are three basic formats that can be used for the When expression as specified in the Trigger Specification dialog:
- Clauses referencing table columns (Table column format )
- Clauses using the SELECT 1 FROM format to establish conditions of execution (SELECT 1 FROM format )
- Clauses using the column condition (Column condition format )
Table column format
The When expression in the Trigger Specification dialog 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) 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:
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.
Column condition format
The When expression can support column-based triggering.
An example of the syntax of the column condition that you would specify in the Trigger Specification dialog of the Database Interface Designer is presented below:
column condition new.part_number = `S'
Because column-based triggering is based on row-based triggering, the Row-based triggering check box must be enabled. You would enter the column condition new.part_number = `S'clause into the When pane. The portion, new.part_number = `S', specified after column condition, is the SQL clause that the Oracle database will process.