15.10 - Example: Non-Valid Use of Subquery in WHEN Clause - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Release Number
15.10
Published
December 2015
Content Type
Programming Reference
Publication ID
B035-1144-151K
Language
English (United States)
Last Update
2018-06-05

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.