15.10 - WHEN - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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

This clause is optional and has no default.

(search_condition)
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), but does not include row-level security columns.
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.
  • If search_condition evaluates to TRUE, then the evaluated statement or row qualifies to fire the triggered action.
  • If search_condition evaluates to FALSE, then the evaluated statement or row does not qualify to fire the triggered action.
  • If search_condition evaluates to UNKNOWN, then the evaluated statement or row does not qualify to fire the triggered action.