Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <21068486.1152632912116.JavaMail.jira@brutus>
Date: Tue, 11 Jul 2006 15:48:32 +0000 (GMT+00:00)
From: "Deepa Remesh (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@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.
In-Reply-To: <1149853848.1125618312287.JavaMail.jira@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

     [ http://issues.apache.org/jira/browse/DERBY-551?page=all ]

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

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

Attaching a patch 'derby-551-patch1-v1.diff' for review/commit. This patch allows invoking procedures in triggered sql statement. It is based on 'derby-551-draft3.diff' Changes are:

* Modifies the parser to allow call statement in the trigger action.

* Changes the validateStatement in InternalTriggerExecutionContext to catch following statements not allowed in triggers:
   - DDL statements are not allowed in triggers. This was caught as an assert failure as this would be caught at compile time in case of direct use of DDL statements. Since use of DDL statement inside a procedure will not be caught at compile time, patch changes the assert to a SQLException.
   - Insert, update, delete statements are not allowed in a before trigger. On the same lines, a procedure that modifies sql data should not be allowed in a before trigger. This is also caught at runtime. This uses the existing check in InternalTriggerExecutionContext.validateStatement. However, the existing check was limited to the trigger table. This check was modified to check for use of insert,update,delete statements on any table.  

* Adds a new test lang/procedureInTrigger.sql to derbylang suite. The test is based on the scenarios in ' ProcedureInTrigger_Tests_v1.html'. Only one case (calling procedures that modify SQL data in before triggers) is handled differently by this patch. Trigger creation will pass but firing will fail. This behaviour will change once we move this check to compile time. So I have not modified the test case document. Also, I have not added the test cases for recursive triggers as I am not sure about the expected behaviour. This is being discussed as part of DERBY-1261 on derby-dev.

* Modifies tests and master files which used to check that call statement cannot be part of trigger action.

Once this issue is resolved, we would need updates to documentation. I will open a sub-task for this later. 

Ran derbyall with both sane and insane jars using Sun jdk 1.4.2 on Windows XP. No failures. Please take a look at this patch.

> 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: 10.1.1.0
>  Environment: All platforms
>     Reporter: Satheesh Bandaram
>     Assignee: Deepa Remesh
>      Fix For: 10.2.0.0
>  Attachments: ProcedureInTrigger_Tests_v1.html, 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-551draft2.diff
>
> 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