11.9 Data Definition Language (DDL) Statements

Generally, it is not possible to run a Data Definition Language (DDL) statement in a database trigger because most databases do not allow mixed DML and DDL transactions. Although virtual triggers do not overcome this transactional limitation, they do allow DDL statements to be executed as a side effect of an XDS event.

For example:

<input xmlns:jdbc="urn:dirxml:jdbc">
    <add class-name="usr">
        <add-attr name="fname">
            <value>John</value>
        </add-attr>
        <add-attr name="lname">
            <value>Doe</value>
        </add-attr>
    </add>
    <jdbc:statement>
        <jdbc:sql>CREATE USER jdoe IDENTIFIED BY novell</jdbc:sql>
    </jdbc:statement>
</input>    

This XML resolves to:

SET AUTOCOMMIT OFF
INSERT INTO indirect.usr(fname, lname) VALUES(’John’, ’Doe’);
COMMIT; -- explicit commit
SET AUTOCOMMIT ON
CREATE USER jdoe IDENTIFIED BY novell;
-- implicit commit

Using the jdbc:transaction-id and jdbc:transaction-type attributes to group DML and DDL statements into a single transaction causes the transaction to be rolled back on most databases. Because DDL statements are generally executed as separate transactions, it is possible that the insert statement in the above example might succeed and the create user statement might roll back.

It is not possible, however, that the insert statement fails and the create user statement succeeds. The driver stops executing chained transactions at the point where the first transaction is rolled back.