Re: Debug DDL trigger

Wolfgang Breitling wrote:

> I could be wrong, but I am pretty sure you are not allowed to issue
> commits - or rollbacks - in a trigger. That rules out not only the
> commits of your inserts, but especially your "execute immediate
> 'create table ...'" as all DDL imply a commit.

Wolfgang, you're never wrong. If it appears that you are wrong, it must be the work of Satan. In this case, however, I can strenghten tour belief with an excerpt from 9i Application developers guide:

      Restrictions on Creating Triggers

Coding triggers requires some restrictions that are not required for standard PL/SQL blocks. The following sections discuss these restrictions.

        Maximum Trigger Size

The size of a trigger cannot be more than 32K.

        SQL Statements Allowed in Trigger Bodies

The body of a trigger can contain DML SQL statements. It can also contain |SELECT| statements, but they must be |SELECT|... |INTO|... statements or the |SELECT| statement in the definition of a cursor.

DDL statements are not allowed in the body of a trigger. Also, no transaction control statements are allowed in a trigger. |ROLLBACK|, |COMMIT|, and |SAVEPOINT| cannot be used.For system triggers, {|CREATE|/|ALTER|/|DROP|} |TABLE| statements and |ALTER|...|COMPILE| are allowed.

-- 
Mladen Gogala
Oracle DBA
Ext. 121



--
http://www.freelists.org/webpage/oracle-l