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 09:02:06 GMT
Thomas Hill <thomas.k.hill@t-online.de> writes:

>> 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,
>> 
>
>
> Hi,
>
> referencing NEW as NEW I could add although not sure why this is needed 
> (e.g. NEW as UPDATEROW or something, but NEW as NEW?). 

Right, the first "NEW" is a keyword required by the syntax¹, and the
second "NEW" is a correlation name which could be any identifier. This
should work too:

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

(the ROW keyword is optional, and might make it clearer that NEW is not
an identifier)

> Is passing the value in the only option? 
> For compatibility with how other data
> base backends (PostgreSQL) do this I would prefer 
> if there would be an option to access the value 
> from within the procedure. 
> Can someone confirm whether this is possible or not in Derby please?

The only way I can think of, is if the RowId column is an identity
column, you can call the IDENTITY_VAL_LOCAL function² from the stored
procedure. See the attachment for an example. One limitation with that
approach is that the function only works if the INSERT statement inserts
a single row at a time.

Hmm... Looking at it again, that won't work either, as the return value
of the IDENTITY_VAL_LOCAL function isn't updated until after the INSERT
statement has completed, which is after the trigger action has
completed. So the value seen in the stored procedure is actually that of
the row inserted by the previous statement, not the current one.

I don't have any other ideas, I'm afraid.

¹ http://db.apache.org/derby/docs/dev/ref/rrefsqlj89752.html
² http://db.apache.org/derby/docs/dev/ref/rrefidentityvallocal.html

-- 
Knut Anders

Mime
View raw message