15.10 - Example: Non-Valid Use of Subquery in WHEN Clause - 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

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.