Debug DDL trigger 2005-08-23 - By Mladen Gogala
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
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Wolfgang Breitling wrote:
<blockquote cite="mid430B2639.2000905@(protected)" type="cite">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.
<br>
</blockquote>
Wolfgang, you're never wrong. If it appears that you are wrong, it
must be the work of Satan. In this case, however,<br>
I can strenghten tour belief with an excerpt from 9i Application
developers guide:<br>
<h3 class="H2"><font color="#330099" face="Arial, Helvetica, sans-serif"
>Restrictions
on Creating Triggers</font></h3>
<!--/TOC=h2-->
<a name="1799"></a>
<p class="BP">Coding triggers requires some restrictions that are not
required for standard PL/SQL blocks. The following sections discuss
these restrictions.</p>
<a name="12438"></a>
<h4 class="SH1"><font face="Arial, Helvetica, sans-serif">Maximum
Trigger Size</font></h4>
<a name="12447"></a>
<p class="BP">The size of a trigger cannot be more than 32K.</p>
<a name="710"></a>
<h4 class="SH1"><font face="Arial, Helvetica, sans-serif">SQL
Statements Allowed in Trigger Bodies</font></h4>
<a name="712"></a>
<p class="BP">The body of a trigger can contain DML SQL statements. It
can also contain <code>SELECT</code> statements, but they must be <code>SELECT<
/code>...
<code>INTO</code>... statements or the <code>SELECT</code> statement
in the definition of a cursor.</p>
<a name="716"></a>
<p class="BP">DDL statements are not allowed in the body of a trigger.
Also, no transaction control statements are allowed in a trigger. <code
>ROLLBACK</code>,
<code>COMMIT</code>, and <code>SAVEPOINT</code> cannot be used.For
system triggers, {<code>CREATE</code>/<code>ALTER</code>/<code>DROP</code>}
<code>TABLE</code> statements and <code>ALTER</code>...<code>COMPILE</code>
are allowed.</p>
<!--AnN_AnchorNote will not be supported in future releases -->
<a name="718"></a>
<hr><br>
<br>
<br>
<pre class="moz-signature" cols="72">--
Mladen Gogala
Oracle DBA
Ext. 121
</pre>
</body>
</html>
|
|
