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

Deepa Remesh updated DERBY-551:
-------------------------------

    Attachment: derby-551-patch3-v1.diff
                derby-551-patch3-v1.status

Attaching a patch 'derby-551-patch3-v1.diff' which disallows creation of before triggers which
contain calls to procedures that modify SQL data. Changes are:

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

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

* 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 PROCEDURE_CALL_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. When I was making this change, I was not sure if this reliability
setting will interfere with any other checks. After going through other places it is used,
I think we are making specific checks to reliability and this setting will not interfere with
other cases. Also, I did not see failures in any of the tests. Can someone please confirm
this usage is correct? 

* Reverts the modified check added in InternalTriggerExecutionContext.validateStatement to
catch the above case at runtime. With the addition of the above compile time check, there
is no need for the check at runtime. 

* Adds a new message. Reuses the same SQLSatate as for invalid statement in triggers.

* Modifies procedureInTrigger.sql test to handle this change.

Ran derbyall with the changes and did not see any failures. However, I made few minor changes
and re-running derbyall now. 

Meantime, I would like to get feedback on this patch mainly to see if the usage of CompilerContext
is correct. 

> 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: 10.1.1.0
>         Environment: All platforms
>            Reporter: Satheesh Bandaram
>         Assigned To: Deepa Remesh
>             Fix For: 10.2.0.0
>
>         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-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
though.

-- 
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

        

Mime
View raw message