16.20 - WHEN - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

A keyword introducing a search condition clause to refine the conditions that fire the trigger.

This clause is optional and has no default.

Logic that further refines the conditions for firing the trigger.
search_condition is any valid search condition based on comparisons of items within the scope of the trigger definition. This includes UDT comparisons as long as ordering has been defined for the UDTs. See CREATE ORDERING and REPLACE ORDERING.
If the trigger is a row trigger, then search_condition is based on row correlation names for the current row.
If search_condition specifies a subquery, then it can contain aggregates. Otherwise, aggregates are not valid as part of a WHEN clause search condition specification.
This is important because any time you reference an OLD TABLE or NEW TABLE transition table from a WHEN clause search condition, the predicate must be expressed as a subquery. Because a WHEN condition must provide a single result, the typical use of OLD TABLE and NEW TABLE column references is with aggregates.
A Boolean condition. Any expression that does not evaluate to TRUE or FALSE is not valid either as a search condition or as a cursor name.
search_condition is evaluated once for each:
  • execution of the triggering statement for statement triggers
  • row of the transition table of changed rows for row triggers
Whether a triggered action is taken or not depends on the evaluation of the search condition result, as described by the following table.
search_condition Evaluation Statement or Row
TRUE Qualifies to fire the triggered action.
FALSE Does not qualify to fire the triggered action.
UNKNOWN Does not qualify to fire the triggered action.