17.10 - Example: Valid 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 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.