db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Peter Nabbefeld <Peter.Nabbef...@gmx.de>
Subject Re: How to use stored procedures with triggers?
Date Sat, 04 Jun 2005 05:28:48 GMT

Thank You for the very good answer, it's obviously been the 'MODE 
DB2SQL' which I was missing. Where can I find an overview what is 
different with this mode? I haven't seen it in the derby manual.

Kind regards

Peter Nabbefeld



Jean T. Anderson schrieb:
> 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