DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: ORA-04092: cannot COMMIT in a trigger

  1. #1
    Join Date
    Dec 2001
    Posts
    141

    Unhappy

    Hi all !

    I want to create a DDL trigger to automatically execute a GRANT SELECT to a role when a table is created.

    I use the following procedures and DDL trigger :

    create or replace trigger tri_grantauto after create on test.schema
    begin
    if ora_dict_obj_type in ('TABLE','VIEW') then
    p_execjob(ora_dict_obj_name, 'TESTROLE');
    end if;
    end;
    /

    create or replace procedure p_execjob (p_objname varchar2,p_grantee varchar2)
    is
    v_job number;
    v_text varchar2(100);
    begin
    v_text := 'p_grantauto('||chr(39)||p_objname||chr(39)||',';
    v_text := v_text ||chr(39)||p_grantee||chr(39)||');';
    dbms_job.submit(v_job, v_text, sysdate+(60/86400));
    dbms_job.run(v_job);
    end;
    /

    create or replace procedure p_grantauto (p_objname varchar2,p_grantee varchar2)
    is
    begin
    execute immediate 'grant select on '||p_objname||' to '||p_grantee;
    end;
    /

    I've tried to create a table and received the ORA-04092 error message.

    I've modified the last procedure to add the PRAGMA AUTONOMOUS_TRANSACTION :
    create or replace procedure p_grantauto (p_objname varchar2,p_grantee varchar2)
    is
    PRAGMA AUTONOMOUS_TRANSACTION;
    begin
    execute immediate 'grant select on '||p_objname||' to '||p_grantee;
    end;
    /

    And it doen't make any difference ...

    Always :
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04092: cannot COMMIT in a trigger
    ORA-06512: at "SYS.DBMS_IJOB", line 405
    ORA-06512: at "SYS.DBMS_JOB", line 267
    ORA-06512: at "TEST.P_EXECJOB", line 9
    ORA-06512: at line 3

    Please help ...

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    From MetaLink:

    All Data Definition Language (DDL) statements execute an implicit commit.
    Even if you create a separate procedure, to execute the DDL statement, the
    execution of the separate procedure within the trigger is still in the same
    transaction, forcing a commit of any statement executed in the trigger and
    hence causing the error ORA-04092.



    Use autonomous transactions to create the table:

    1) Create a procedure called my_procedure that will execute as an autonomous
    transaction:

    SQL> create or replace procedure my_procedure
    2 is
    3 pragma AUTONOMOUS_TRANSACTION;
    4 BEGIN
    5 execute immediate 'CREATE TABLE my_table (id NUMBER)';
    6 END;
    7 /

    Procedure created.

    2) Create the trigger, that will call the autonomous procedure to create the
    table:

    SQL> create or replace trigger my_trig after delete on emp
    2 BEGIN
    3 my_procedure;
    4 END;
    5 /

    Trigger created.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width