Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

exceptions handling in compound trigger

ciaspolaMar 4 2013 — edited Mar 5 2013
Hi all
what fallows is an excerpt taken from Oracle doc available to this link:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#g1043102

*"In most cases, if a trigger runs a statement that raises an exception, and the exception is not handled by an exception handler, then the database rolls back the effects of both the trigger and its triggering statement.*
*...*
*In the case of a compound DML trigger, the database rolls back only the effects of the triggering statement, not the effects of the trigger. However, variables declared in the trigger are re-initialized, and any values computed before the triggering statement was rolled back are lost."*

To replicate the described behavior of compound triggers that's what I tried (all in the same session)

CREATE table COMMIT_LOG (
STMT VARCHAR2(200) );

CREATE TABLE COMMIT_BASE (
BASE VARCHAR2(200) );

INSERT INTO VALUES ('Some words');
INSERT INTO VALUES ('Some words');

CREATE OR REPLACE TRIGGER EXCEPTION_RAISER
FOR INSERT OR UPDATE OR DELETE ON COMMIT_BASE
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
INSERT INTO COMMIT_LOG VALUES ('Here it happened: ' || ORA_DICT_OBJ_NAME);
END BEFORE STATEMENT;
AFTER STATEMENT IS
BEGIN
raise_application_error(-20000, 'Details in commit_log.');
END AFTER STATEMENT;
END EXCEPTION_RAISER;
/

UPDATE COMMIT_BASE SET BASE='Words';

as expected the exception was thrown and not caught by any handler:

Error starting at line 194 in command:
update commit_base set base='Exceptions in triggers'
Error report:
SQL Error: ORA-20000: Details in commit_log.
ORA-06512: at "PLSQL.EXCEPTION_RAISER", line 8
ORA-04088: error during execution of trigger 'PLSQL.EXCEPTION_RAISER'

unfortunately COMMIT_LOG stays empty and COMIIT_BASE untouched (as expected).
The question is : why the INSERT statement in the trigger is rolled back?

Any hint will be appriciated
Thanks in advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 2 2013
Added on Mar 4 2013
2 comments
607 views