Example: Calling a UDF From a Trigger - 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 example calls a user-defined function (internal form) named firstlerr from a trigger.

The relevant CREATE TABLE definitions are as follows:

     CREATE TABLE trg_udf007_04 (
       a INTEGER, 
       b FLOAT);
     CREATE TABLE trg_udf007_03 (
       a INTEGER, 
       b FLOAT);
     CREATE TABLE trg_udf007_01 (
       a INTEGER, 
       b FLOAT);

Insert a row into trg_udf007_01:

     INSERT INTO trg_udf007_01 
     VALUES (4, 2.5);

Create the following UDF body:

#define SQL_TEXT Latin_Text
#include <udfdefs.h>
 /* Select statement:
 CREATE FUNCTION first1err(integer, float)
RETURNS float
LANGUAGE C
NO SQL
EXTERNAL NAME 'sc!first1err!first1err.c';
 */
void first1err(INTEGER  *a,
FLOAT    *b,
FLOAT    *result,
int      *indc_a,
int      *indc_b,
int      *indc_result,
char     sqlstate[6],
SQL_TEXT extname[129],
SQL_TEXT specific_name[129],
SQL_TEXT error_message[257])
{
if (*indc_a == -1 || *indc_b == -1)
{
*indc_result = -1;
return;
}
*result = *a + *b;
*indc_result = 0;
/* handle warning */
if (*a == -1 )
{
      strcpy(sqlstate, "01H01");
      strcpy((char *) error_message, "You have been warned no nulls");
      return;
}
       /* create a divide fault */
    if (*a == -2 )
      {
      int  f2 = 2;
      int  f1 = 0;
 volatile       int  f3 = 99999;
      f3 = f2/f1;
      if (f3 < 0)
          f3 = 5; 
      return;
      }
    if ( *result < 0.0 )
      {
      strcpy(sqlstate, "22H01");
      strcpy((char *) error_message, "This is a user created error.");
      return;
      }
}

Create the following trigger:

     CREATE TRIGGER trg_udf007_01_trigger
       AFTER INSERT ON trg_udf007_03 
       REFERENCING NEW AS cur
     FOR EACH ROW
       WHEN ( 11 > first1err(cur.a, cur.b))
      (INSERT INTO trg_udf007_04 (cur.a, cur.b); 
      );

Note that the WHEN clause of this trigger definition calls the UDF named firstlerr.

Perform the following INSERT request:

     INSERT INTO trg_udf007_03 
       SELECT * 
       FROM trg_udf007_01;

The output produces one row in trg_udf007_03 and one row in trg_udf007_04.