Example: Dynamic UDT Input From a Trigger - 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™

The following example creates an AFTER INSERT row trigger that specifies a NEW VARIANT_TYPE constructor expression in its WHEN clause.

First, create the trigger.

     CREATE TRIGGER Dyn_TestTrigger02
       AFTER INSERT ON Source_DynTriggerTest2
       REFERENCING NEW AS NewRow
     FOR EACH ROW
       WHEN (scalar001dynRtnint_1p(NEW VARIANT_TYPE(NewRow.a AS a,
                                                    NewRow.b AS b))<8)
         INSERT INTO Target_DynTriggerTest2
         VALUES(1,NewRow.a,NewRow.b);

Assume that there are no rows in Target_DynTriggerTest2. Now insert a row into Source_DynTriggerTest2 , which invokes the newly created trigger, Dyn_TestTrigger02, to insert a row into Target_DynTriggerTest2 if the evaluation of the scalar UDF scalar001dynRtnint_1p satisfies the specified WHEN clause condition.

     INSERT INTO Source_DynTriggerTest2 
     VALUES (3,3,3);

Select all columns from Target_DynTriggerTest2:

     SELECT * 
     FROM Target_DynTriggerTest2;
      *** Query completed. One row found. 3 columns returned.
      *** Total elapsed time was 1 second.
        Integer1      NewColA      NewColB
     -----------  -----------  -----------
               1            3            3

As expected given the definition for trigger DynTestTrigger02 , the newly inserted row in Target_DynTriggerTest2 contains the values 1, 3, and 3 for its three columns.