Re: after insert trigger calling a procedure - how can I retrieve NEW values inside the procedure?

[Sylvain Leroux]

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