Example: Invoking an SQL UDF Within a Trigger Definition - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

This example invokes the SQL UDF common_value_expression from the WHEN clause of the definition of the row trigger trig_insert_after_update.

     CREATE TRIGGER trig_insert_after_update
       AFTER UPDATE OF b1 ON t1
       REFERENCING OLD AS OldRow NEW AS NewRow
     FOR EACH ROW
       WHEN (test.common_value_expression(OldRow.a1, NewRow.b1) >.10)
       INSERT INTO t2
       VALUES (NewRow.a1, NewRow.b1, NewRow.c1);

The next example invokes the SQL UDF common_value_expression in the triggered statement of the row trigger trig_insert.

     CREATE TRIGGER trig_insert 
       AFTER INSERT ON t1
       REFERENCING NEW AS n
     FOR EACH ROW (
       INSERT INTO t2 
       VALUES (n.a1, test.common_value_expression(n.b2, 1), n.c1); );