before update trigger

[Stephen Ince <si...@opendemand.com>]

Hi,
   I am currently trying to port over a schema from postgres and I am little 
stuck.

1) Does derby support before update triggers. If not is there a plan to 
support this feature?
e.g.
CREATE TRIGGER setIfServiceKeysOnUpdate2Trigger
BEFORE UPDATE ON outages

2) Does derby support calling a procedure from a trigger? I can't seem to 
get this to work.
CREATE TRIGGER setIfServiceKeysOnUpdate2Trigger
BEFORE UPDATE ON outages
FOR EACH ROW MODE DB2SQL
CALL myprocy();

3) I noticed that db2 supports the "when" clause, but derby doesn't. Are 
there any plans to support some type "if" statement for a trigger.

Steve 

Re: before update trigger

[Bryan Pendleton <bp...@amberpoint.com>]

>    I was able to get the synatx correct. My next question is can you 
> pass in the new and old row into a procedure call.
> I am geting syntax errors.

Hi Stephen, I'm afraid I don't know what might be wrong, hopefully
somebody else on the list can help you.

Can you post the exact text of the syntax errors that you get? If
there is a stack trace or any other information in the derby.log
file, that might be useful too.

thanks,

bryan

Re: before update trigger

[Daniel John Debrunner <dj...@apache.org>]

Stephen Ince wrote:
> Brian,
>    Thanx for your response. I will vote for it. I will definitely 
> concider joining the development effort.
> 
>    I was able to get the synatx correct. My next question is can you 
> pass in the new and old row into a procedure call.

No, just the individual columns/values of the old and new rows.

Dan.

Re: before update trigger

[Stephen Ince <si...@opendemand.com>]

Brian,
    Thanx for your response. I will vote for it. I will definitely concider 
joining the development effort.

    I was able to get the synatx correct. My next question is can you pass 
in the new and old row into a procedure call.
I am geting syntax errors.
e.g.

CREATE PROCEDURE setIfServiceKeysOnUpdate(IN newRow ResultSet,
IN oldRow ResultSet)
PARAMETER STYLE JAVA CONTAINS SQL LANGUAGE JAVA EXTERNAL NAME
'org.opennms.database.derby.DerbyFunctions.setIfServiceKeysOnUpdate';

CREATE TRIGGER setIfServiceKeysOnUpdate1Trigger
NO CASCADE BEFORE
UPDATE ON outages
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW MODE DB2SQL
call setIfServiceKeysOnUpdate(NEW,OLD);

java method.
public static void setIfServiceKeysOnUpdate(ResultSet oldRow, ResultSet 
newRow)
{
:
}

Steve
----- Original Message ----- 
From: "Bryan Pendleton" <bp...@amberpoint.com>
To: "Derby Discussion" <de...@db.apache.org>
Sent: Wednesday, July 04, 2007 2:14 PM
Subject: Re: before update trigger


> The ability to use a WHEN clause in a trigger is logged
> as DERBY-534. If you're interested in this issue, you could
> vote for it, or (better!) join the development community and
> contribute an implementation.
> https://issues.apache.org/jira/browse/DERBY-534
>
> I think the other issues you raised (before update triggers,
> and triggers calling procedures) should work. I think that
> you may have to say "NO CASCADE" for a BEFORE trigger, and I think
> that you may have to say "CALL PROCEDURE", not just "CALL".
>
> Here's some good background information:
> http://db.apache.org/derby/docs/10.2/ref/rrefcreateprocedurestatement.html
> http://db.apache.org/derby/docs/10.2/ref/rrefcreateprocedurestatement.html
> http://db.apache.org/derby/docs/10.2/devguide/cdevspecial27163.html
> http://db.apache.org/derby/docs/10.2/devguide/cdevspecial42117.html
>
> If you still can't get them to work, try posting the transcript
> of the actual IJ session that you tried, with the exact statements
> you sent, and the errors that were returned.
>
> This should help people spot the problem and offer more precise 
> suggestions.
>
> thanks,
>
> bryan
>
> 

Re: before update trigger

[Bryan Pendleton <bp...@amberpoint.com>]

The ability to use a WHEN clause in a trigger is logged
as DERBY-534. If you're interested in this issue, you could
vote for it, or (better!) join the development community and
contribute an implementation.
https://issues.apache.org/jira/browse/DERBY-534

I think the other issues you raised (before update triggers,
and triggers calling procedures) should work. I think that
you may have to say "NO CASCADE" for a BEFORE trigger, and I think
that you may have to say "CALL PROCEDURE", not just "CALL".

Here's some good background information:
http://db.apache.org/derby/docs/10.2/ref/rrefcreateprocedurestatement.html
http://db.apache.org/derby/docs/10.2/ref/rrefcreateprocedurestatement.html
http://db.apache.org/derby/docs/10.2/devguide/cdevspecial27163.html
http://db.apache.org/derby/docs/10.2/devguide/cdevspecial42117.html

If you still can't get them to work, try posting the transcript
of the actual IJ session that you tried, with the exact statements
you sent, and the errors that were returned.

This should help people spot the problem and offer more precise suggestions.

thanks,

bryan