12 Replies Latest reply: Jan 25, 2013 4:16 AM by John Stegeman RSS

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

    936666
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        >
                        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
                          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
                            Thanks for the point.
                            • 11. Re: SQL Error: ORA-04092: cannot COMMIT in a trigger
                              BluShadow
                              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
                                Karthick is absolutely correct.... dont do that. Its wrong idea
                                So is DDL in a trigger