2 Replies Latest reply: May 2, 2013 2:42 PM by Solomon Yakobson RSS

    Trigger for to insert other rows

    muttleychess
      Hi

      I must to build triggers that insert other two rows when the user insert the first record, but the First record must to change Seq to 2 (was 1) then in trigger to insert other record with seq equal 1 and more other record with seq equal 800, I tried some ways , but return error

      First insert the user from application
         INSERT INTO ARCTB_ACAO_IMEDIATA ( CD_ARC,  CD_TIPO_ACAO,  CD_ACAO_IMEDIATA,  DS_ACAO,  CD_RESPONSAVEL,  DT_ORIGINAL,  DT_ABERTURA,  NR_PRAZO,DT_PREVISTA, DS_HISTORICO ) VALUES (9732, 0,1, 'TESTE','ucrwilma', To_date('02/05/2013','DD/MM/YYYY'), To_date('02/05/2013','DD/MM/YYYY'), 1, To_date('02/05/2013','DD/MM/YYYY'), '');
      I create triggers
      CREATE OR REPLACE TRIGGER ARCTR_ACAO_IMEDIATA_I
      BEFORE INSERT
      
      ON ARCTB_ACAO_IMEDIATA REFERENCING NEW AS New OLD AS Old
      
      FOR EACH ROW
      DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
      BEGIN
         IF :NEW.CD_ACAO_IMEDIATA = 1 THEN
            :NEW.CD_ACAO_IMEDIATA:=2;
         END IF;   
      END ARCTR_ACAO_IMEDIATA_I;
      CREATE OR REPLACE TRIGGER ARCTR_ACAO_IMEDIATA_IU
      AFTER INSERT OR UPDATE
      
      ON ARCTB_ACAO_IMEDIATA REFERENCING NEW AS New OLD AS Old
      
      FOR EACH ROW
      DECLARE
          PRAGMA AUTONOMOUS_TRANSACTION;
      ..... 
      ....
      IF  :NEW.CD_ACAO_IMEDIATA =2  AND :NEW.CD_TIPO_ACAO = 0  AND :NEW.DS_ACAO!='Validar Investigacao' THEN
          -------
          -- 800
          ------
          INSERT INTO ARCTB_ACAO_IMEDIATA (CD_ACAO_IMEDIATA,
            CD_ARC,
            CD_TIPO_ACAO,
            DS_ACAO,
            CD_RESPONSAVEL,
            DT_ORIGINAL,
            DT_ABERTURA,
            NR_PRAZO,
            DT_PREVISTA,USUARIO)
           VALUES (800,:new.cd_arc,
              0,
             'Validar Investigacao',
              v_COORD_NUCLEO,
              trunc(sysdate),
              trunc(sysdate),1,trunc(sysdate),V_CLIENF_INFO);
            --  :new.cd_acao_imediata:=2;
             
              
           -- update   ARCTB_ACAO_IMEDIATA 
           --     set cd_acao_imediata = 2
           --  where CD_ARC = :new.cd_arc
            -- and  cd_acao_imediata =1;
             --commit;
             
            ------
            --700
            ------
            INSERT INTO ARCTB_ACAO_IMEDIATA (CD_ACAO_IMEDIATA,
            CD_ARC,
            CD_TIPO_ACAO,
            DS_ACAO,
            CD_RESPONSAVEL,
            DT_ORIGINAL,
            DT_ABERTURA,
            DT_REALIZADO,
            NR_PRAZO,
            DT_PREVISTA,USUARIO)
           VALUES (1,:new.cd_arc,
              0,
             'Provavel motivo da reclamacao',
              V_COORD_INVESTIGA,
               trunc(sysdate),
               trunc(sysdate),null,
              1,
              trunc(sysdate),V_CLIENF_INFO);
           commit;
      
        END IF;
      ...
      
      ...
      END ARCTR_ACAO_IMEDIATA_IU
      return me error
      ORA-06519: active autonomous transaction detected and rolled back
      ORA-06512: at "CLIBGF.ARCTR_ACAO_IMEDIATA_IU", line 221
      ORA-04088: error during execution of trigger 'CLIBGF.ARCTR_ACAO_IMEDIATA_IU'
      ORA-06512: at line 7
      using 9.2.08
        • 1. Re: Trigger for to insert other rows
          Solomon Yakobson
          Most likely trigger ARCTR_ACAO_IMEDIATA_IU doesn't commit/rollback. You have commit inside IF statement, but what about code after IF statement? All we see is elipses.

          SY.
          • 2. Re: Trigger for to insert other rows
            L-MachineGun
            This seems to be a very "Flawed" design.
            Also, you cannot perform dml on the same table the trigger belongs to.
            If you actually need to do it, then create a view and use "instead of" trigger -- or -- use one of the standard "mutating trigger" work-around.
            :p
            PS: There exist many caveats in the use of autonomous transactions to 'fix' mutating table error -- they must be
            used with caution and the knowledge of how the transaction is really progressing.