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 Sat, 15 Jul 2006 15:27:16 GMT
     [ http://issues.apache.org/jira/browse/DERBY-551?page=all ]

Deepa Remesh updated DERBY-551:

    Attachment: derby-551-patch3-v2.diff

Attaching a patch 'derby-551-patch3-v2.diff' which addresses Dan's comments. 

Summary of patch:

* Adds a new reliability bit mask(MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL) to CompilerContext.

* Sets the above reliability for before triggers before binding the actionNode in CreateTriggerNode.

* Checks the above reliability in CallStatementNode. After the called procedure is resolved,
the sql allowed by the procedure is checked and if it is "modifies sql data" and if we have
set the reliability to MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL, it means we are calling a procedure
that modifies sql data in the action statement of a before trigger. In this case, an exception
is thrown and trigger creation fails. 

* Removes the check in InternalTriggerExecutionContext.validateStatement which was used to
catch the use of DML in before triggers. This is a redundant check as use of DML in before
triggers is now caught at compile time. Added a comment to indicate this.

* Adds a new message to indicate procedures that modify sql data are not allowed in before
triggers. Reuses the same SQLState as for invalid statement in triggers.

* Modifies the test lang/procedureInTrigger.sql to handle this change. 

With this patch, ran derbyall with Sun jdk 1.4.2 on Windows XP. No failures related to the
patch. Seeing 7 failures also seen in Tinderbox tests (http://www.multinet.no/~solberg/public/Apache/TinderBox_Derby/Limited/testSummary-422045.html).

This patch will resolve this issue. There are two open items for which I will open new JIRAs:
* New issue to handle case where a procedure called in trigger's action statement can cause
recursion. Trigger recursion is being discussed as part of DERBY-1261. 
* Sub-task to change documentation to indicate support of this feature.

Please take a look at this patch. Thanks.

> 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
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions:
>         Environment: All platforms
>            Reporter: Satheesh Bandaram
>         Assigned To: Deepa Remesh
>             Fix For:
>         Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status,
derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status,
derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551-patch3-v2.diff,
derby-551-patch3-v2.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
> 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: http://issues.apache.org/jira/secure/Administrators.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message