Peter Nabbefeld wrote:
In the thread mentioned before, I've been told to use a function in a
VALUES clause. So I've asked 'CREATE TRIGGER ... FOR EACH STATEMENT
VALUES(...) doesn't work. Will I really have to insert a dummy value
into some dummy table to call a procedure or function from a trigger? '
Here's a very simple (contrived) example just to demonstrate the basic
syntax.
My user-defined SQL function takes two values: a data value and a value
by which to increment it. Here's the Java source code for the SQL function:
import java.sql.*;
import java.util.*;
public class MyMathFuncs
{
public static int myInc(int inc_val, int int_val) throws SQLException
{
if((inc_val < 0) || (int_val < 0) )
throw new SQLException ("Input args must be > 0", "38555");
int retval = inc_val + int_val;
return retval;
}
}
Compile that code, then create the function and test it like this:
ij> create function myInc
(increment_value integer, int_value integer)
returns integer
language java parameter style java
no sql
external name 'MyMathFuncs.myInc';
ij> values myInc (5,10);
1
-----------
15
ij> values myInc(-1,5);
1
-----------
ERROR 38555: Input args must be > 0
It basically works, so now I'm ready to put it into a trigger.
Here's my table:
create table foo
(myId int generated always as identity, myValue int not null);
And I'll use VALUES with my new function in a trigger to prevent bad
values from being entered (like a check constraint):
create trigger fooTrig
after insert on foo referencing NEW as N
for each row mode db2sql
values myInc(N.myValue, 5);
Here's a simple test that shows the behavior:
ij> insert into foo (myValue) values (5);
1 row inserted/updated/deleted
ij> select * from foo;
MYID |MYVALUE
-----------------------
1 |5
1 row selected
ij> insert into foo (myValue) values (-5);
ERROR 38555: Input args must be > 0
As I said at the beginning, this is pretty contrived, but it should give
you something simple to test locally to see if you can get the VALUES
syntax working.
-jean
