Example: Valid WHEN Clause - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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);

Result:

     *** Failure 3514 User-generated transaction ABORT.