db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: trigger calling a procedure
Date Wed, 24 Oct 2012 06:39:34 GMT
Thomas Hill <thomas.k.hill@t-online.de> writes:

> Rick Hillegas <rick.hillegas@...> writes:
>
>> 
>> On 10/21/12 11:45 PM, Thomas Hill wrote:
>> > CREATE TRIGGER "TR_XY"
>> >    AFTER INSERT
>> >    ON "TBL_XY"
>> >    FOR EACH ROW
>> >    CALL PROCEDURE "SP_xy"('xyz', 0);
>> Hi Thomas,
>> 
>> That's almost correct. If you remove the keyword PROCEDURE from the 
>> triggered statement, then it will be a valid SQL statement. The 
>> following would work:
>> 
>> CREATE TRIGGER "TR_XY"
>>    AFTER INSERT
>>    ON "TBL_XY"
>>    FOR EACH ROW
>>    CALL "SP_xy"('xyz', 0);
>> 
>> Hope this helps,
>> -Rick
>> 
>> 
>
> thanks for that! working now.
>
> Next challenge for me is to figure out if and how the NEW values can be read and
> stored in a variable in the java code? 
>
> Tried 
>
> int i = NEW."RowID"; 
>
> to store the new value of column "RowID" in a variable, but the java compiler
> already complains about that (NEW cannot be resolved to a type / class <NEW>
> cannot be resolved to a type / syntax error on token "RowID" : class expected)

Hi Thomas,

I think you'd need to add a REFERENCING clause to the trigger definition
and pass in the new value as an argument to the procedure. Something
like:

  CREATE TRIGGER "TR_XY"
     AFTER INSERT
     ON "TBL_XY"
     REFERENCING NEW AS NEW
     FOR EACH ROW
     CALL "SP_xy"('xyz', 0, NEW."RowID")

Hope this helps,

-- 
Knut Anders

Mime
View raw message