17.10 - Example: Non-Valid Use of Subquery in WHEN Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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.