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.
