Using triggers and procedures?

[P.N.]

Hello,

I want a procedure to be called when a record is added or deleted.
However, Derby doesn't support it, and will not in 10.1 (aacording to
the manual).

1. Is there a workaround for this? I want to call some static Java method.

2. When will that be supported?

Kind regards

Peter Nabbefeld

[Jean T. Anderson]

A trigger can't call a procedure, but it can call a user-defined sql
function.

Could you provide a few more specifics about what your method needs to do?

  -jean

Peter Nabbefeld wrote:

>
> Hello,
>
> I want a procedure to be called when a record is added or deleted.
> However, Derby doesn't support it, and will not in 10.1 (aacording to
> the manual).
>
> 1. Is there a workaround for this? I want to call some static Java method.
>
> 2. When will that be supported?
>
> Kind regards
>
> Peter Nabbefeld
>

[P.N.]

Jean T. Anderson schrieb:
> A trigger can't call a procedure, but it can call a user-defined sql
> function.
>
> Could you provide a few more specifics about what your method needs to do?
>
I just want to give a feedback to my app, if the number of records in
the database has changed (independently if I use the embedded db or not).

I've now created a function using
CREATE FUNCTION APPBUT_USER.TRIGGER_RESIZE(SCHEMA_NAME VARCHAR(255),
TABLE_NAME VARCHAR(255)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE
JAVA READS SQL DATA EXTERNAL NAME 'myFunc'

How can I use this function in a trigger? I'm always getting syntax errors.

Kind regards

Peter Nabbefeld

>  -jean
>
> Peter Nabbefeld wrote:
>
>>
>> Hello,
>>
>> I want a procedure to be called when a record is added or deleted.
>> However, Derby doesn't support it, and will not in 10.1 (aacording to
>> the manual).
>>
>> 1. Is there a workaround for this? I want to call some static Java
>> method.
>>
>> 2. When will that be supported?
>>
>> Kind regards
>>
>> Peter Nabbefeld
>>
>
>

[Suavi Ali Demir]

You may need to use it in a values clause in the
trigger body:

VALUES(APPBUT_USER.TRIGGER_RESIZE(schema,table))

Regards,
Ali

--- Peter Nabbefeld <[hidden email]> wrote:

> Jean T. Anderson schrieb:
> > A trigger can't call a procedure, but it can call
> a user-defined sql
> > function.
> >
> > Could you provide a few more specifics about what
> your method needs to do?
> >
> I just want to give a feedback to my app, if the
> number of records in
> the database has changed (independently if I use the
> embedded db or not).
>
> I've now created a function using
> CREATE FUNCTION
> APPBUT_USER.TRIGGER_RESIZE(SCHEMA_NAME VARCHAR(255),
>
> TABLE_NAME VARCHAR(255)) RETURNS INTEGER LANGUAGE
> JAVA PARAMETER STYLE
> JAVA READS SQL DATA EXTERNAL NAME 'myFunc'
>
> How can I use this function in a trigger? I'm always
> getting syntax errors.
>
> Kind regards
>
> Peter Nabbefeld
>
>
> >  -jean
> >
> > Peter Nabbefeld wrote:
> >
> >>
> >> Hello,
> >>
> >> I want a procedure to be called when a record is
> added or deleted.
> >> However, Derby doesn't support it, and will not
> in 10.1 (aacording to
> >> the manual).
> >>
> >> 1. Is there a workaround for this? I want to call
> some static Java
> >> method.
> >>
> >> 2. When will that be supported?
> >>
> >> Kind regards
> >>
> >> Peter Nabbefeld
> >>
> >
> >
>
>

[Satheesh Bandaram]

You can use your function in any expressions. The easiest way to invoke
a function is by using VALUES clause.

VALUES APPBUT_USER.TRIGGER_RESIZE(......);

You can also use the function in SELECT list, like SELECT
APPBUT_USER.TRIGGER_RESIZE(......) WHERE ...

Both these can be used in trigger body.

Hope that helps,
Satheesh

Peter Nabbefeld wrote:

> Jean T. Anderson schrieb:
>
>> A trigger can't call a procedure, but it can call a user-defined sql
>> function.
>>
>> Could you provide a few more specifics about what your method needs
>> to do?
>>
> I just want to give a feedback to my app, if the number of records in
> the database has changed (independently if I use the embedded db or not).
>
> I've now created a function using
> CREATE FUNCTION APPBUT_USER.TRIGGER_RESIZE(SCHEMA_NAME VARCHAR(255),
> TABLE_NAME VARCHAR(255)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE
> JAVA READS SQL DATA EXTERNAL NAME 'myFunc'
>
> How can I use this function in a trigger? I'm always getting syntax
> errors.
>
> Kind regards
>
> Peter Nabbefeld
>
>
>>  -jean
>>
>> Peter Nabbefeld wrote:
>>
>>>
>>> Hello,
>>>
>>> I want a procedure to be called when a record is added or deleted.
>>> However, Derby doesn't support it, and will not in 10.1 (aacording
>>> to the manual).
>>>
>>> 1. Is there a workaround for this? I want to call some static Java
>>> method.
>>>
>>> 2. When will that be supported?
>>>
>>> Kind regards
>>>
>>> Peter Nabbefeld
>>>
>>
>>
>
>
>

[P.N.]

Suavi Ali Demir schrieb:
> You may need to use it in a values clause in the
> trigger body:
>
> VALUES(APPBUT_USER.TRIGGER_RESIZE(schema,table))
>

CREATE TRIGGER ... FOR EACH STATEMENT VALUES(...) doesn't work. Will I
really have to insert a dummy value into some dummy table to call a
procedure or function from a trigger?

Regards

Peter

> Regards,
> Ali
>
> --- Peter Nabbefeld <[hidden email]> wrote:
>
>>Jean T. Anderson schrieb:
>>
>>>A trigger can't call a procedure, but it can call
>>
>>a user-defined sql
>>
>>>function.
>>>
>>>Could you provide a few more specifics about what
>>
>>your method needs to do?
>>
>>I just want to give a feedback to my app, if the
>>number of records in
>>the database has changed (independently if I use the
>>embedded db or not).
>>
>>I've now created a function using
>>CREATE FUNCTION
>>APPBUT_USER.TRIGGER_RESIZE(SCHEMA_NAME VARCHAR(255),
>>
>>TABLE_NAME VARCHAR(255)) RETURNS INTEGER LANGUAGE
>>JAVA PARAMETER STYLE
>>JAVA READS SQL DATA EXTERNAL NAME 'myFunc'
>>
>>How can I use this function in a trigger? I'm always
>>getting syntax errors.
>>
>>Kind regards
>>
>>Peter Nabbefeld
>>
>>
>>
>>> -jean
>>>
>>>Peter Nabbefeld wrote:
>>>
>>>
>>>>Hello,
>>>>
>>>>I want a procedure to be called when a record is
>>
>>added or deleted.
>>
>>>>However, Derby doesn't support it, and will not
>>
>>in 10.1 (aacording to
>>
>>>>the manual).
>>>>
>>>>1. Is there a workaround for this? I want to call
>>
>>some static Java
>>
>>>>method.
>>>>
>>>>2. When will that be supported?
>>>>
>>>>Kind regards
>>>>
>>>>Peter Nabbefeld
>>>>
>>>
>>>
>>
>