db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sylvain Leroux <s...@wanadoo.fr>
Subject Re: after insert trigger calling a procedure - how can I retrieve NEW values inside the procedure?
Date Thu, 20 Aug 2009 12:04:34 GMT
Thomas Hill a écrit :
> Hi,
> 
> have created the following procedure and trigger:
> 
> CREATE PROCEDURE "TF_ClientsAI"()
> LANGUAGE JAVA
>   PARAMETER STYLE JAVA
>   MODIFIES SQL DATA
>   EXTERNAL NAME 'derbyPk.Functions.TF_ClientsAI'; 
> 
> -- DROP TRIGGER "TR_ClientsAI";
> 
> CREATE TRIGGER "TR_ClientsAI"
> AFTER INSERT
> ON rte."Clients"
> REFERENCING NEW AS NEW
> FOR EACH ROW 
> CALL DERBY."TF_ClientsAI"();
Is it a requirement for you to call an *external* procedure to do the 
jod? Otherwise, you could use an SQL statement directly in your trigger.

Something like:
CREATE TRIGGER "TR_ClientsAI"
     AFTER INSERT
     ON rte."Clients"
     REFERENCING NEW AS NEW
     FOR EACH ROW
     INSERT INTO rte."EntityAuditLog" ("ClientID")
         VALUES (NEW.ClientID);

If you have to use an external procedure, you must pass the required 
values as a parameter in the function call:

CREATE TRIGGER "TR_ClientsAI"
     AFTER INSERT
     ON rte."Clients"
     REFERENCING NEW AS NEW
     FOR EACH ROW
     CALL DERBY."TF_ClientsAI"(NEW."ClinetID");

Hope this helps,
Sylvain

-- 
Website: http://www.chicoree.fr



Mime
View raw message