db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Deepa Remesh (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-551) Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
Date Thu, 29 Jun 2006 21:04:32 GMT
     [ http://issues.apache.org/jira/browse/DERBY-551?page=all ]

Deepa Remesh updated DERBY-551:

    Attachment: derby-551-draft1.diff

Attaching a draft patch 'derby-551-draft1.diff' (not for commit) which enables procedures
to be called from triggers. This patch is not complete. I am posting it as I would like to
get some feedback. Changes made by the patch are:

* Modify the parsing to allow call statement inside trigger. 

* Check that procedures that modify SQL data are not allowed in BEFORE triggers. To check
this, I used AliasDescriptor in DataDictionary to get the alias info. I have placed this check
currently in routineInvocation(). I think this is causing a diff in lang/releaseCompileLocks.sql.
There is an entry in the lock_table when there should be none:
TABLE|IS  |SYSALIASES                                                                    
                                                 |Tablelock           |GRANT

Earlier, I had tried to use the RoutineAliasInfo in the MethodCallNode but this had not worked
as this object was null at trigger creation time. Any suggestions to do this in a better way?

* A new message is added to indicate the above error. It maps to the same SQL state as that
for unsupported statements in triggers. 

* Added a new test. I have to add some more test cases to this. I am adding tests based on
the scenarios identified in ProcedureInTrigger_Tests_v1.html. Please go through this and let
me know if there are scenarios that need to be added/removed.

* Modified two tests by removing statements that were testing that call statements are not
allowed in triggers.

I would appreciate feedback on the patch and the test scenarios.

> 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