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.