15.10 - Example: Valid 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 valid because an aggregate appears on the right-hand side of the search condition, and the left-hand side of the inequality condition is a constant.

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

If you insert values into t1 and the WHEN condition is satisfied, then the triggered action statement, ABORT, performs and a failure message is returned.

     INSERT INTO t1 (1, 1, 1);
     *** Failure 3514 User-generated transaction ABORT.