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

Mime
View raw message