Example: Calling a UDF From a Trigger - Advanced SQL Engine - Teradata Database

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.