db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Counting subscribers
Date Fri, 30 Jun 2006 18:06:20 GMT
Sushil Khekare wrote:
>  I want to write a trigger for example here is what we wrote in mysql
>
> Mysql
> trigger*****************************************************************
> *****
>
> CREATE TRIGGER testcalc1 BEFORE INSERT ON emailworddtls
> FOR EACH ROW BEGIN
> DECLARE wId VARCHAR(255) DEFAULT '';
> SELECT WMappingID into wId FROM wordmst where WMappingID =
> NEW.WMappingID;
> IF wId = '' THEN 
> INSERT INTO wordmst (WMappingID, occurrenceinallemails)
> VALUES(NEW.WMappingID,0);
> END IF;
> END;
>
> ************************************************************************
> ***********
>
> I tried with following type in derby it works but what I am interested
> in is use of variables like 'wId'.
>
> Derby*******************************************************************
> ***********
>
> CREATE TRIGGER testcalc after INSERT ON emailworddtls
> REFERENCING new AS insertedrow
> FOR EACH ROW mode db2sql
> UPDATE wordmst SET occurrenceinallemails = occurrenceinallemails +
> insertedrow.Occurrence where WMappingID = insertedrow.WMappingID;
>
> ************************************************************************
> ***********
>
> Is it possible using derby?
>
> Sushil
>
>   
Hi -
The mysql example you show uses the mysql API and hence can use 
variables.  To duplicate this in Derby you need to use the Derby API, 
that is to say, you need to use JDBC.  The basic steps to doing this is:

1) create a java class with a method that does what you need and can be 
executed as a function.
2) store the compiled class in the DB
3) define it as a function in the database
4) Use the function in the SQL statement of the trigger

It always helps to have an example to work with when performing a new 
task like this and the one I often refer to is at:        
http://wiki.apache.org/db-derby/SendEmailRoutine
    This shows creating both a procedure and a function from the method 
and using both in different ways.  The example of calling the function 
from the trigger is the one most pertinent to your question.

Also take a look at the overview of using JAVA to extent the 
capabilities of Derby at:
    http://wiki.apache.org/db-derby/DerbySQLroutines
A lot can be done using java procedures and functions.




Mime
View raw message