exceptions handling in compound trigger
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