16.20 - Example: Non-Valid Use of Subquery in WHEN Clause - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

The following WHEN clause is not valid because the subquery in the search condition returns multiple values and therefore cannot evaluate to either TRUE or FALSE:

     CREATE TRIGGER trigwhen4
       AFTER INSERT ON t1
       REFERENCING NEW AS NewRow
     FOR EACH ROW
       WHEN (t2.a > (
         SELECT b 
         FROM t2 
         WHERE t2.c < 5))
     ABORT;

If you insert multiple values into t1, the SELECT request returns an “unknown” response, which the WHEN condition cannot evaluate, and the transaction fails.

     *** Failure 3669 More than one value was returned by a subquery.