db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean T. Anderson" <...@bristowhill.com>
Subject Re: How to use stored procedures with triggers?
Date Fri, 03 Jun 2005 22:56:49 GMT
Peter Nabbefeld wrote:

> In the thread mentioned before, I've been told to use a function in a 
> 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 

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);

    ij> values myInc(-1,5);
    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.


View raw message