Insert Trigger 1

[sonik]

Hi All,<br><br>I have an row level after insert trigger on table DEPT which calls a stored procedure to dynamically create a trigger on a different table based on the values inserted into table DEPT. I get the following error :<br><br>ORA-04092: cannot COMMIT in a trigger<br>ORA-06512: at &quot;SCOTT.PKG_GENERATE_TRIGGERS&quot;, line 166<br>ORA-06512: at &quot;SCOTT.PKG_GENERATE_TRIGGERS&quot;, line 277<br>ORA-06512: at &quot;SCOTT.TRG_RA_INS_DEPT&quot;, line 3<br>ORA-04088: error during execution of trigger 'SCOTT.TRG_RA_INS_DEPT'<br><br>I understand that you cannot have any commits inside the trigger body, however the same happens on an Alter trigger based at the schema level which is supposed to allow CREATE statements in the trigger body.&nbsp;&nbsp;Does anyone know how I get around this problem.<br>I am using Oracle8i 8.1.5.<br><br>Any help is appreciated greatly.<br><br>Thanks<br><br>Ketan<br><br><br>

 

[marcelb]

Hi Ketan,<br><br>You can try to use autonomous transaction (a new feature of 8i). All you have to do is to add a PRAGMA AUTONOMOUS_TRANSACTION in your procedure:<br><br>CREATE PROCEDURE ... AS PRAGMA AUTONOMOUS_TRANSACTION;<br>BEGIN<br>&nbsp;&nbsp;...<br>END;<br><br>You only have to know that this transaction will be commited despite of your main transaction success or failure.

 

[bhallar]

Hi,<br>Its not possible to write any DDL statement within the trigger body as each DDL fires the COMMIT statement before execute.