How to access the record in a java method call by a trigger

[Patrice Drolet <pd...@infodata.ca>]

Hi,

I need to call a java method from a trigger that will access the 
record.  The examples I saw shows only sql (select or updates) that can 
use external methods (with values ()). This is ok for some needs but the 
called method needs a reference to the row being modified. Here is how I 
see it:

create trigger fooTrig after insert on foo referencing NEW as N for each 
row mode db2sql values myIncTable(N);

But it does not work because this returns an error:

create function myIncTable(rs java.sql.ResultSet) language java 
parameter style java modifies sql external name  
'ca.infodata.derby.medoffice.triggers.LesTriggers.myIncTable';

I see 2 problems here: it seems that only functions can be used in a 
trigger but functions can not use MODIFIES SQL (I need the called 
trigger method to do inserts in different tables and it cannot be done 
by adding other triggers). And the function creation does not seem to 
accept a ResultSet parameter.

Is there any solution to this problem?

Regards,

Patrice Drolet
Logiciels INFO-DATA inc.

Re: How to access the record in a java method call by a trigger

[Satheesh Bandaram <sa...@Sourcery.Org>]

About your second problem... I don't think you can pass a resultset to a
function as an argument. Instead, you should be able to pass individual
fields of the row, like 'values myIncTable(N.column1, N.column2)'. You
would then create your function with matching number of arguments and types.

Derby functions are read-only, so you can't update or insert in them...
Derby procedures can insert so it would great to CALL derby procedures
in triggers, but this is not supported currently. It should be a fairly
easy enhancement to Derby to allow procedure invocation in after-triggers.

Satheesh

Patrice Drolet wrote:

> Hi,
>
> I need to call a java method from a trigger that will access the
> record.  The examples I saw shows only sql (select or updates) that
> can use external methods (with values ()). This is ok for some needs
> but the called method needs a reference to the row being modified.
> Here is how I see it:
>
> create trigger fooTrig after insert on foo referencing NEW as N for
> each row mode db2sql values myIncTable(N);
>
> But it does not work because this returns an error:
>
> create function myIncTable(rs java.sql.ResultSet) language java
> parameter style java modifies sql external name 
> 'ca.infodata.derby.medoffice.triggers.LesTriggers.myIncTable';
>
> I see 2 problems here: it seems that only functions can be used in a
> trigger but functions can not use MODIFIES SQL (I need the called
> trigger method to do inserts in different tables and it cannot be done
> by adding other triggers). And the function creation does not seem to
> accept a ResultSet parameter.
>
> Is there any solution to this problem?
>
> Regards,
>
> Patrice Drolet
> Logiciels INFO-DATA inc.
>
>
>