This discussion is archived
12 Replies Latest reply: Jan 25, 2013 2:16 AM by John Stegeman RSS

SQL Error: ORA-04092: cannot COMMIT in a trigger

936666 Newbie
Currently Being Moderated
Trying to drop the table inside the trigger but i'm unable to do it.

SQL Error: ORA-04092: cannot COMMIT in a trigger

I need to drop the table based on the some condition say condition is the archive table with more than millions of records which is of no use so i plan to drop the table.
I will be inserting the the unwanted table to mytable ,mytable which is having the trigger will fire to drop the table.
I need this to be done on automatic basis so i have chosen trigger.
is there anyway of automatic other than trigger in this case.
  • 1. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    Karthick_Arp Guru
    Currently Being Moderated
    933663 wrote:
    Trying to drop the table inside the trigger but i'm unable to do it.

    SQL Error: ORA-04092: cannot COMMIT in a trigger

    I need to drop the table based on the some condition say condition is the archive table with more than millions of records which is of no use so i plan to drop the table.
    I will be inserting the the unwanted table to mytable ,mytable which is having the trigger will fire to drop the table.
    I need this to be done on automatic basis so i have chosen trigger.
    is there anyway of automatic other than trigger in this case.
    You can't COMMIT inside a trigger. Oracle issue an auto COMMIT before and after the execution of DDL. So you can't use DDL in trigger. You may get suggestion to use AUTONOMOUS_TRANSACTION to perform COMMIT within tirgger. But dont do that. Its wrong idea.

    I will suggest you look back into your requirement and see what exactly you want. You could schedule a job that runs on a daily basis that will pick up the object details from your table and drop them accordingly.
  • 2. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    Paul Horth Expert
    Currently Being Moderated
    933663 wrote:
    Trying to drop the table inside the trigger but i'm unable to do it.

    SQL Error: ORA-04092: cannot COMMIT in a trigger

    I need to drop the table based on the some condition say condition is the archive table with more than millions of records which is of no use so i plan to drop the table.
    I will be inserting the the unwanted table to mytable ,mytable which is having the trigger will fire to drop the table.
    I need this to be done on automatic basis so i have chosen trigger.
    is there anyway of automatic other than trigger in this case.
    No, a trigger is definitely not the way to do it.

    Write a PL/SQL procedure to do this.
  • 3. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    936666 Newbie
    Currently Being Moderated
    So can i create a trigger to have a job in it ?
    i need all the things instantly not on the periodic basis.
    so i can have a job inside the tiger which will be having the stored procedure name which intrun drops the table?
    correct me if i am wrong.

    Thanks!
  • 4. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    Karthick_Arp Guru
    Currently Being Moderated
    933663 wrote:
    So can i create a trigger to have a job in it ?
    i need all the things instantly not on the periodic basis.
    so i can have a job inside the tiger which will be having the stored procedure name which intrun drops the table?
    correct me if i am wrong.

    Thanks!
    Don't do it in a trigger. Trigger is a child transaction and it should be successfull only when the parent is sucessfull.

    Consider this example.

    You insert a table name EMP into your table. A trigger gets fired which drops the table EMP. But in the subsequent transaction the insert fails.

    So now what? You don't have an entry for EMP in your table but the table is dropped.

    Trigger is a very bad idea for your requirement. On a second thought your requirement also sounds like a bad idea.
  • 5. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    Paul Horth Expert
    Currently Being Moderated
    933663 wrote:
    So can i create a trigger to have a job in it ?
    i need all the things instantly not on the periodic basis.
    so i can have a job inside the tiger which will be having the stored procedure name which intrun drops the table?
    correct me if i am wrong.

    Thanks!
    You're wrong.

    Forget triggers.

    Do your insert into mytable from the archive table and the check for the number of rows (and subsequent drop if necessary) inside a procedure.
  • 6. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    936666 Newbie
    Currently Being Moderated
    There is no insert transaction in my case there is no reads/ writes to this table for so long time so i preferred to drop the table.
  • 7. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    971895 Journeyer
    Currently Being Moderated
    Sample code...
    CREATE OR REPLACE TRIGGER MD_CONNECTIONS_TRG  after INSERT OR UPDATE ON emp FOR EACH ROW
    declare
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      begin  
        execute immediate 'drop table test';
                 
    END;
  • 8. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    rp0428 Guru
    Currently Being Moderated
    >
    There is no insert transaction in my case there is no reads/ writes to this table for so long time so i preferred to drop the table.
    >
    That isn't what you said to begin with. You said you are using an INSERT
    >
    I will be inserting the the unwanted table to mytable ,mytable which is having the trigger will fire to drop the table.
    >
    If you insert to mytable and the insert fails or the transaction gets rolled back the unwanted table will be gone even though the insert did not take place.

    Triggers are NOT transactional and should NOT be used for things like this.

    Write a stored procedure that saves the data to 'mytable', commits that data and then drops the unwanted table.
  • 9. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    John Stegeman Oracle ACE
    Currently Being Moderated
    968892,

    Don't do that. There's a reason why Oracle doesn't want you committing in triggers. Think about these things:

    a). Triggers could, in fact, fire more than once for a given DML event
    b). Just because a trigger fires doesn't mean that the actual DML event is going to be ultimately successful (i.e. the actual update or insert could fail even if the trigger succeeds - now what? You've dropped the table but the DML didn't succeed)

    Furthermore, dropping tables in a trigger seems like a silly idea in the first place.

    John
  • 10. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    936666 Newbie
    Currently Being Moderated
    Thanks for the point.
  • 11. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    BluShadow Guru Moderator
    Currently Being Moderated
    LOL,

    Karthick said:
    You may get suggestion to use AUTONOMOUS_TRANSACTION to perform COMMIT within tirgger. But dont do that. Its wrong idea.
    Then you come along with
    968892 wrote:
    Sample code...
    CREATE OR REPLACE TRIGGER MD_CONNECTIONS_TRG  after INSERT OR UPDATE ON emp FOR EACH ROW
    declare
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    begin  
    execute immediate 'drop table test';
    
    END;
    Classic case of showing you don't have a clue of the implications of what that will be doing.

    Karthick is absolutely correct.... dont do that. Its wrong idea
  • 12. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
    John Stegeman Oracle ACE
    Currently Being Moderated
    Karthick is absolutely correct.... dont do that. Its wrong idea
    So is DDL in a trigger

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points