You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Peter Nabbefeld <Pe...@gmx.de> on 2005/06/03 11:10:29 UTC
How to use stored procedures with triggers?
Hello,
I've been asking for how to use stored procedures with triggers (Thread:
'Using triggers and procedures?'), but my last question (just to make
sure there's no better way) has not been answered - probably the thread
has been "too old" (in terms of the mailing list traffic) :-( .
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? '
Will I have to use 'DECLARE GLOBAL TEMPORARY TABLE ...'? And, as I think
autocommit is the default, will it be cleared automatically after each
INSERT? I'd prefer to have every inserted record deleted as soon as
possible, as it'd be used only as a dummy table, so memory consumption
should be as small as possible :-) .
Kind regards
Peter Nabbefeld
Re: How to use stored procedures with triggers?
Posted by Peter Nabbefeld <Pe...@gmx.de>.
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
>
Re: How to use stored procedures with triggers?
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
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
Re: How to use stored procedures with triggers?
Posted by Peter Nabbefeld <Pe...@gmx.de>.
Satheesh Bandaram schrieb:
> Which version of Derby are you using? There was a known issue with using
> functions in values clause inside a trigger in 10.0 that got fixed in
> later versions.
>
I'm using 10.0.2.1, as it is considered stable. My problem has probably
been that I've not used 'MODE DB2SQL'. However, derby now complains
about missing schema 'user', while I've prefixed everything with my own
schema (seems to be ignored).
Kind regards
Peter Nabbefeld
> Satheesh
>
> 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? '
>>
>>Will I have to use 'DECLARE GLOBAL TEMPORARY TABLE ...'? And, as I
>>think autocommit is the default, will it be cleared automatically
>>after each INSERT? I'd prefer to have every inserted record deleted as
>>soon as possible, as it'd be used only as a dummy table, so memory
>>consumption should be as small as possible :-) .
>>
>>Kind regards
>>
>>Peter Nabbefeld
>>
>>
>>
>
>
>
Re: How to use stored procedures with triggers?
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Which version of Derby are you using? There was a known issue with using
functions in values clause inside a trigger in 10.0 that got fixed in
later versions.
Satheesh
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? '
>
> Will I have to use 'DECLARE GLOBAL TEMPORARY TABLE ...'? And, as I
> think autocommit is the default, will it be cleared automatically
> after each INSERT? I'd prefer to have every inserted record deleted as
> soon as possible, as it'd be used only as a dummy table, so memory
> consumption should be as small as possible :-) .
>
> Kind regards
>
> Peter Nabbefeld
>
>
>