1 2 Previous Next 15 Replies Latest reply: Sep 29, 2010 4:17 AM by 789895 RSS

    oracle trigger commit

    707781
      I wrote a trigger to capture a field change.
      it inserts a record to audit table when the field value changes.
      I am a newbie.

      is the record inserted when the change is commited by default?

      or any script to set it (on commit)
        • 1. Re: oracle trigger commit
          679747
          Yes

          You need to explicitly mention commit.Inside a trigger you can write commit as given below
          CREATE OR REPLACE TRIGGER bef_ins_emp
          
          BEFORE INSERT ON emp FOR EACH ROW
          
          DECLARE
          
             PRAGMA AUTONOMOUS_TRANSACTION;
          
          BEGIN
          
             INSERT INTO emp_audit VALUES (
          
                :new.ename, 'BEFORE INSERT', SYSDATE);
          
             COMMIT;
          
          END;
          Thanks
          AJ
          • 2. Re: oracle trigger commit
            Toon Koppelaars
            is the record inserted when the change is commited by default?
            I assume you mean: is the insert committed, when the change (that fired the trigger) is commited?

            The answer would then be: yes.

            I see no reason why you would want to make the trigger an autonomous-transaction and have it commit just the insert.
            • 3. Re: oracle trigger commit
              Tubby
              I wouldn't recommend putting the autonomous transaction directly in the trigger. Mostly because of human error (or the potential to it).

              If someone comes along and see the trigger, misses the autonomous transaction declaration (or doesn't know what it is and the ramifications are) then you've got quite a mess on your hands.

              Also, if you end up needing more functionality in the trigger (outside of pure auditing, which is one of the VERY few cases i'd actually recommend using an autonomous transaction in) you would need to create another trigger, which gets ugly from a maintenance / understandability perspective.

              A better (in my opinion anyway) approach would be something like
              create table emp as select * from scott.emp where 1 = 0;
              
              create table emp_audit
              (
                 ename       varchar2(30),
                 log_action  varchar2(10),
                 log_date    date
              );
              
              create or replace procedure log_emp_audit
              (
                 p_emp_audit    emp_audit%rowtype
              )
              is
                 PRAGMA AUTONOMOUS_TRANSACTION;
              begin
              
                 insert into emp_audit values p_emp_audit;
                 commit;
              
              end log_emp_audit;
              /
              
              
              CREATE OR REPLACE TRIGGER bef_ins_emp
              BEFORE INSERT ON emp FOR EACH ROW
              declare
                 l_emp_audit    emp_audit%rowtype;
              BEGIN
              
                 l_emp_audit.ename       := :new.ename;
                 l_emp_audit.log_action  := 'INSERT';
                 l_emp_audit.log_date    := sysdate;
              
                 log_emp_audit (l_emp_audit);
              
              END;
              /
              
              TUBBY_TUBBZ?insert into emp select * from scott.emp where rownum = 1;
              
              1 row created.
              
              Elapsed: 00:00:00.62
              TUBBY_TUBBZ?rollback;
              
              Rollback complete.
              
              Elapsed: 00:00:00.57
              TUBBY_TUBBZ?select * from emp_audit;
              
              ENAME                                                                                      LOG_ACTION                     LOG_DATE
              ------------------------------------------------------------------------------------------ ------------------------------ --------------------------
              SMITH                                                                                      INSERT                         27-SEP-2010 03 04:41
              
              1 row selected.
              
              Elapsed: 00:00:00.93
              TUBBY_TUBBZ?
              
              TUBBY_TUBBZ?select * from emp;
              
              no rows selected
              
              Elapsed: 00:00:00.51
              • 4. Re: oracle trigger commit
                Tubby
                Forreging wrote:
                I wrote a trigger to capture a field change.
                it inserts a record to audit table when the field value changes.
                I am a newbie.

                is the record inserted when the change is commited by default?

                or any script to set it (on commit)
                I'm not entirely sure i understood your question. Are you asking how to make the audit data persist even when the transaction does a rollback (please check my last post and you will see a demonstration of that working)?

                If not, can you clarify your question?
                • 5. Re: oracle trigger commit
                  707781
                  dear all,

                  I havent stated clearly.

                  I just wanna ask

                  if the field is changed but not commited, will it insert a new record into audit table (by default)?
                  • 6. Re: oracle trigger commit
                    679747
                    Till the moment you wont explicitly commit it it wont gets saved.So once you perform commit then it gets saved.

                    Thanks
                    AJ
                    • 7. Re: oracle trigger commit
                      Tubby
                      Forreging wrote:
                      dear all,

                      I havent stated clearly.

                      I just wanna ask

                      if the field is changed but not commited, will it insert a new record into audit table (by default)?
                      Assuming no Autonomous Transaction is in place then the insert into the audit table will happen, and will either persist (if the session performs a commit) or not (if the session performs a rollback) just the same as the modified data that fired the trigger (the trigger is bound by the transactional boundaries of the session assuming you don't use an Autonomous Transaction).
                      • 8. Re: oracle trigger commit
                        672680
                        You don`t, can`t and no need to do COMMIT in trigger except the autonomous transaction.
                        It is in the same transaction with the operation that invokes it, so rollback or commit to that operation will rollback or commit the change done by trigger.

                        Trigger as below:
                        create or replace
                        TRIGGER TEST 
                        BEFORE insert ON EMP 
                        FOR EACH ROW 
                        begin
                        
                          INSERT INTO EMP_AUDIT VALUES (:NEW.EMPNO, :NEW.ENAME, SYSDATE);
                        END;
                        First test result with NO COMMIT in trigger:
                        SQL>  INSERT INTO EMP VALUES (8081, 'BAI', 'CLERK', 8080, SYSDATE, 5000, NULL, 30);
                        
                        1 row created.
                        
                        SQL> select * from emp_audit;
                        
                             EMPNO ENAME      INS_DATE
                        ---------- ---------- ---------
                              8081 BAI        28-SEP-10
                        
                        SQL> rollback;
                        
                        Rollback complete.
                        
                        SQL> select * from emp_audit;
                        
                        no rows selected
                        
                        SQL>
                        Second test result with COMMIT in trigger:
                        SQL> INSERT INTO EMP VALUES (8081, 'BAI', 'CLERK', 8080, SYSDATE, 5000, NULL, 30);
                        INSERT INTO EMP VALUES (8081, 'BAI', 'CLERK', 8080, SYSDATE, 5000, NULL, 30)
                        *
                        ERROR at line 1:
                        ORA-04092: cannot COMMIT in a trigger
                        ORA-06512: at "PHOENIX.TEST", line 4
                        ORA-04088: error during execution of trigger 'PHOENIX.TEST'
                        • 9. Re: oracle trigger commit
                          789895
                          Hi,

                          It would have the same result as your original table. Once a commit is issued the data is persisited in your original table as well as the table that gets manipulated through the trigger. Once a rollback is issued the data is not persisited in your original table as well as the table manipulated through the trigger. You need not do any explicit TCL in a trigger.

                          cheers

                          VT
                          • 10. Re: oracle trigger commit
                            801305
                            Hi CST,

                            But in my case there is no autonomous transaction defined & hence trigger is not able to update the required changes for all* the inserts which invoke the trigger. Although for some inserts, the trigger is able to update the changes.

                            I have defined a before insert trigger for each row, which updates the average of particular field from particular table (not the table which invoked this trigger). I'm confused what to do. If I define the trigger as autonomous transaction & commit the changes and if the original insert is rollbacked by the application then also this update will still be commited. [ I do not have the access to the application code, so I don't know when & where the commit is given. I can only alter the trigger. ] Please suggest a way out.

                            Regrds
                            • 11. Re: oracle trigger commit
                              789895
                              Hi,

                              Welcome to Forums.

                              Basically as a good practice it is not adviced to terminate a transaction within a trigger. I would suggest you to change the way you handle the details in the trigger. Do not treat it as an autonomous transaction as there is all means that the original statement that triggers the change you are making in the other table via trigger can deem to fail. Also check your trigger if you have any conditions defined such that only particular inserts trigger the changes.

                              cheers

                              VT

                              PS:

                              Guess you are from Western part of India, The VT in my signature does not correspond to (Victoria Terminus) and should not be changed to CST which i dislike as it is the initials of my last name and first name which i will not tolerate to be changed ;)
                              • 12. Re: oracle trigger commit
                                801305
                                Hi sir, thnx for the reply. I did not mean it. It was just a humourous way to start.

                                I do not have any conditions for responding to only particular inserts. I'll post the trigger itself below.

                                CREATE OR REPLACE TRIGGER HMSTRANS.trg_ST_GRNDT1
                                BEFORE INSERT OR UPDATE
                                ON HMSTRANS.T_T_ST_GRNDT REFERENCING NEW AS New OLD AS Old
                                FOR EACH ROW
                                DECLARE
                                Rate1 NUMBER(10,2) ;
                                Rate2 NUMBER(10,2) ;
                                Rate NUMBER(10,2) ;

                                BEGIN
                                /*
                                dbms_output.put_line('Before Trigger fired...'||:old.n_item_id||' '||:new.n_item_id );
                                */
                                SELECT ROUND ((AVG (n_purchasecost ) * ( SUM (n_totalqty) - SUM (n_freeqty))) / SUM (n_totalqty), 2), COUNT(*) INTO Rate1, Cnt
                                FROM T_T_ST_GRNDT grndt where grndt.N_ITEM_ID = :new.N_ITEM_ID
                                group by N_ITEM_ID ;

                                Rate2 := (:new.n_purchasecost * ( :new.n_totalqty - :new.n_freeqty )) / :new.n_totalqty;

                                Rate := ( Rate1 + Rate2 ) / 2;
                                /*
                                dbms_output.put_line('Rate:'||rate||' Rate1:'||rate1||' Rate2:'||rate2);
                                */
                                UPDATE hmsmaster.T_M_IN_ITEMHD im set N_AVGPURCOST = Rate where im.N_ITEM_ID = :new.N_ITEM_ID ;

                                EXCEPTION
                                WHEN OTHERS THEN
                                NULL;

                                -- Consider logging the error and then re-raise
                                -- RAISE;
                                END trg_ST_GRNDT1;
                                /

                                Every evening I run the query to see how many items are received that day in GRNDT table. But that many number of items are not found to be updated in the ITEMHD with the average purchase cost by this trigger. The purpose is to have the patest purchase cost defined in the master as & when the new receipt is made in the stores.

                                Should I define it as pragma autonomous transaction & give the commit after update?

                                Regrds
                                • 13. Re: oracle trigger commit
                                  789895
                                  Hi,

                                  Are you sure about the table ITEMHD has all the item_id corresponding to the item_id of GRNDT? Can you execute the following query
                                  SELECT COUNT(*) FROM GRNDT WHERE ITEM_ID NOT IN (SELECT ITEM_ID FROM ITEMHD);
                                  and see if there are some mismatch between the tables.

                                  cheers

                                  VT

                                  PS:

                                  No need to call me sir, because we are just sharing our knowledge and not teaching others.
                                  • 14. Re: oracle trigger commit
                                    801305
                                    Thnx buddy,

                                    There are zero mismatches. There is a referential integrity constraint between the two. So no chance.

                                    My only doubt is that the application may not be issuing coimmit on each save & redo must be getting generated. But then also when it writes to data file, it must update into itemhd also. My problem is I can not see the code. It is being maintained by the vendor.

                                    Regrds
                                    HHS
                                    1 2 Previous Next