Systems Engineering and RDBMS

Using commit and rollback in triggers

Posted by decipherinfosys on March 31, 2009

Normally, the usage of a commit or a rollback is not recommended in triggers.  But there are certain situations in which we have to use commit or rollback in triggers.  One of the prevalent usages of commit in trigger is to avoid the mutating table error encountered during trigger execution prior to 11g. Other way to avoid this error is use of compound trigger introduced in Oracle 11g. We will cover mutating trigger in a different blog post…today we will show how can we use commit/rollback in triggers.

To use database control statements in triggers, we have to use compiler directive ‘pragma autonomous_transaction’. When we define trigger as autonomous, it becomes independent and does not belong to current transaction. Since trigger is not part of current transaction, use of commit is allowed in the trigger when declared with pragma. This holds true for any package, procedure and/or function defined with pragma directive. Let us start with an example. We will create two tables and trigger to explain the scenario. Example is based on the one mentioned in oracle documentation.

CREATE TABLE TEST
(
COL1 NUMBER(9) PRIMARY KEY,
COL2 VARCHAR2(30),
COL3 NUMBER(5)
);

CREATE TABLE AUDIT_TEST
(
COL1 NUMBER(9),
COL2 VARCHAR2(30),
OLD_COL3 NUMBER(5),
NEW_COL3 NUMBER(5),
COL4 DATE
);

INSERT INTO TEST
SELECT rownum,’HELLO..’||to_char(rownum), rownum*1000
FROM user_Tables;

Now create following trigger with commit statement in the trigger body.

CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF COL3
ON TEST
FOR EACH ROW
BEGIN
INSERT INTO audit_test(col1,col2,new_col3,old_col3,col4)
VALUES(: old.col1, : old.col2, : new.col3, : old.col3,sysdate);

COMMIT;

END;
/

Trigger will get created without any error. But whenever we update test table, we run into following error.

SQL> UPDATE TEST
2  SET COL3 = 2000
3  WHERE col1 = 1;
UPDATE TEST
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at “SCOTT.TUA_TEST”, line 5
ORA-04088: error during execution of trigger ‘SCOTT.TUA_TEST’

Now let us create same trigger with pragma autonomous_transaction and execute the update statement again.

CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF COL3
ON TEST
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO audit_test(col1,col2,new_col3,old_col3,col4)
VALUES(: old.col1, : old.col2, :new.col3, : old.col3,sysdate);

COMMIT;

END;
/

/* update statement*/
SQL> update test
2  set col3 = 2000
3  where col1 = 1;

1 row updated.

This time update went through fine since trigger is declared autonomous As mentioned earlier, it is not a routine requirement to use control statements in triggers but under certain scenarios where we have to use control statements, this is the way to use it.

Resources:

  • Oracle 11g PL/SQL Reference Manual – here.

3 Responses to “Using commit and rollback in triggers”

  1. A reader had recently asked this question in response to this post:

    /***********************************************/
    Rollback into commit: Oracle 10g

    CREATE OR REPLACE TRIGGER REGISTRA_TRANSACCION
    BEFORE INSERT ON INTERFACE_PRO_ORA
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    LC_MENSAJE VARCHAR2(3000);
    LC_PLSQL VARCHAR2(30000);
    BEGIN

    –CADENA PLSQL
    LC_PLSQL := ‘BEGIN :1 := ‘||’INTER.’|| :NEW.FUNCION ;
    LC_PLSQL := LC_PLSQL ||’(‘||:NEW.TRAMA||’); END;’;

    –EJECUTAMOS LA FUNCION
    EXECUTE IMMEDIATE LC_PLSQL
    USING OUT LC_MENSAJE;

    if substr(LC_MENSAJE, 1, 2) ‘OK’ then
    –rollback desde el trigger
    RAISE_APPLICATION_ERROR(-20001,LC_MENSAJE);
    end if;

    –ACTUALIZAMOS LA RESPUESTA …
    :NEW.RESPUESTA := LC_MENSAJE;

    END REGISTRA_TRANSACCION;

    NOTE: Please, send me yours comments
    /***********************************************/

    Here is the answer:

    You can use raise_application_error in trigger. Oracle stops the execution of transaction once it hits the raise_Application_error.
    Any code after raise_Application_error will not be executed. You need to handle commit/rollback in your calling program from which you are firing the insert statement. Let us go through your example only.We have created sample objects so that we can create trigger definition exactly for our testing purpose.

    connect inter/inter@orcl

    –Create table
    CREATE TABLE INTERFACE_PRO_ORA
    (
    TRAMA VARCHAR2(10),
    FUNCION VARCHAR2(10),
    RESPUESTA VARCHAR2(100)
    );

    — Create function
    CREATE OR REPLACE FUNCTION TEST(p_TRAMA VARCHAR2)
    RETURN VARCHAR2 IS
    p_opt VARCHAR2(100);
    BEGIN

    p_opt := p_trama;

    return p_opt;
    END;
    /

    –Create trigger
    /* We have to change it in order to compile it successfully. */

    CREATE OR REPLACE TRIGGER REGISTRA_TRANSACCION
    BEFORE INSERT ON INTERFACE_PRO_ORA
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    LC_MENSAJE VARCHAR2(3000);
    LC_PLSQL VARCHAR2(30000);

    BEGIN
    –CADENA PLSQL
    LC_PLSQL := ‘BEGIN :1 := ‘||’INTER.’|| :NEW.FUNCION ;
    LC_PLSQL := LC_PLSQL ||'(‘||””||:NEW.TRAMA||””||’); END;’;
    –EJECUTAMOS LA FUNCION
    EXECUTE IMMEDIATE LC_PLSQL USING OUT LC_MENSAJE;

    –dbms_output.put_line(LC_PLSQL);
    –dbms_output.put_line(LC_MENSAJE);

    if substr(LC_MENSAJE, 1, 2) = ‘OK’ then
    –rollback desde el trigger
    RAISE_APPLICATION_ERROR(-20001,LC_MENSAJE);
    end if;

    –ACTUALIZAMOS LA RESPUESTA …
    :NEW.RESPUESTA := LC_MENSAJE;

    END REGISTRA_TRANSACCION;
    /

    — insert new record into table
    BEGIN
    INSERT INTO INTERFACE_PRO_ORA(TRAMA,FUNCION) VALUES(‘NOK Text’,’TEST’);
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    rollback;
    END;

    –select record
    column trama format A10;
    column funcion format A10;
    column RESPUESTA format A10;

    select * from INTERFACE_PRO_ORA;

    SQL> select * from INTERFACE_PRO_ORA;

    TRAMA FUNCION RESPUESTA
    ———- ———- ———-
    NOK Text TEST NOK Text

    It shows that record is successfully inserted. If you notice that we haven’t put commit in our insert block. so let us rollback our change and you will see that after rollback record is not there.

    SQL> rollback;

    Rollback complete.

    SQL> select * from INTERFACE_PRO_ORA;

    no rows selected

    –Now let us change the text so that we can raise an exception in trigger code.

    — insert new record into table
    BEGIN
    INSERT INTO INTERFACE_PRO_ORA(TRAMA,FUNCION) VALUES(‘OK Text’,’TEST’);
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    rollback;
    END;

    Since the trigger raised an exception, insert already failed but if you have some other related DML in same transaction (BEGIN..END block), if they have gone through fine, then your changes are partial which violates basic rule of either committing entire transaction or none. Check out the following code:

    BEGIN

    INSERT INTO INTERFACE_PRO_ORA(TRAMA,FUNCION) VALUES(‘NOK Text’,’TEST’);

    INSERT INTO INTERFACE_PRO_ORA(TRAMA,FUNCION) VALUES(‘OK Text’,’TEST’);
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    –rollback;
    END;

    We have commented out the rollback from calling PL/SQL block. so first insert will go fine and second insert will raise an exception and will be rolled back. That is why it is important to rollback when we catch the exception and rollback the entire transaction.

    As mentioned in the blog post, commit or rollback is not required in trigger since trigger is part of same transaction which populates the table and as a result fires of the trigger. It is used most widely to avoid mutating table error. Don’t forget to catch raised exception in calling program though.

  2. Anonymous said

    thanks a lot for your post

  3. Anonymous said

    good

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: