db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel John Debrunner (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-551) Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
Date Fri, 07 Jul 2006 20:59:32 GMT
    [ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12419777 ] 

Daniel John Debrunner commented on DERBY-551:

I think there are a couple of issues with the approach to checking for procedures that modify
data in a before trigger.

1) The field modifiesSqlData is parser wide, not specific to a single routine. This may cause
issues when the call statement is like:


      This may work, but may be making false assumptions about the ordering of evaluating

       Also I don't see the field being reset at any time.

2) There is an existing mechanism for checking which routines are allowed, I would have expected
this code to use the same functionality.
After looking into it, I see that it is a runtime check, not a compile time check.

The existing runtime check is to support the fact that SQL statements executed in a Java method
are not known when the routine is created (in SQL) since they are executed using dynamic JDBC.
So if a routine is declared as READS SQL DATA then when it is executed by Derby its level
of 'SQL allowed' is set in the statement context . Then any JDBC statements executed by the
Java method are checked against the current level. This includes routines, so a procedure
that is declared READS SQL DATA will throw an exception at runtime if its Java method attempts
to prepare an INSERT SQL statement, or  a CALL statement that executes another  SQL  procedure
that is declared MODIFIES SQL DATA.
See StatementContext.setSQLAllowed(short, boolean);

So we could use the existing mechanism, which means a before trigger with a CALL procedure
that is declared MODIFIES SQL DATA statement would succeed but would fail at runtime when

> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement
in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>          Key: DERBY-551
>          URL: http://issues.apache.org/jira/browse/DERBY-551
>      Project: Derby
>         Type: New Feature

>   Components: SQL
>     Versions:
>  Environment: All platforms
>     Reporter: Satheesh Bandaram
>     Assignee: Deepa Remesh
>      Fix For:
>  Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be
great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have
SQL procedure language, triggers can only execute a single SQL statement. If we allow stored
procedures in triggers, it would be possible to write a trigger that involves more than just
one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators:
For more information on JIRA, see:

View raw message