Francois, do you think that I should ask to the Derby-Dev mail list?

On 8/21/07, Francois Orsini <francois.orsini@gmail.com> wrote:
Thanks for catching this Dan.

DDL in Transaction is supported as per the manual:
A schema manipulation statement (DDL) is not automatically committed when it is performed, but participates in the transaction within which it is issued.
http://db.apache.org/derby/docs/dev/devguide/devguide-single.html#cdevconcepts19173

Then, I don't know why the restriction is there in the code to prevent DDL in a trigger context. I checked the ANSI SQL-2003 specs and I haven't seen (at least obvious) restrictions of schema manipulation/change statement within some trigger action context. If anyone knows...

The comments in the code are not saying much about the reason for the restriction:
java/engine/org/apache/derby/impl/sql/execute/InternalTriggerExecutionContext.java

    /**
     * Make sure that whatever statement is about to be executed
     * is ok from the context of this trigger.
     * <p>
     * Note that we are sub classed in replication for checks
     * for replication specific language.
     *
     * @param constantAction the constant action of the action
     *    that we are to validate
     *
     * @exception StandardException on error
     */
    public void validateStatement(ConstantAction constantAction) throws StandardException
    {

        // DDL statements are not allowed in triggers. Direct use of DDL
        // statements in a trigger's action statement is disallowed by the
        // parser. However, this runtime check is needed to prevent execution
        // of DDL statements by procedures within a trigger context.
         if (constantAction instanceof DDLConstantAction) {
            throw StandardException.newException(SQLState.LANG_NO_DDL_IN_TRIGGER, triggerd.getName(), constantAction.toString());
        }
       
        // No INSERT/UPDATE/DELETE for a before trigger. There is no need to
         // check this here because parser does not allow these DML statements
         // in a trigger's action statement in a before trigger. Parser also
         // disallows creation of before triggers calling procedures that modify
         // SQL data.  
       
    }

--francois


On 8/20/07, Daniel John Debrunner <djd@apache.org > wrote:
Francois Orsini wrote:
> Javier,
>
> I guess and I may be wrong that the main reason for not allowing DDL
> operation in a trigger is due to the fact that a DDL operation will get
> implicitly committed,

DDL is not implicitly committed in Derby.

Dan.