5 Replies Latest reply on Feb 4, 2013 2:25 AM by marksmithusa

    trigger mutating and delete tables

    988668
      hello
      i have to update 2 tables one is mutating wich i already resolve, the other no and both get the values for a third table , after i update the 2 tables, i have to delete the values of the third one .

      But if i use the instruccion FOLLOWS this dont do anyything and dont delete the values. I cant use the compound triggers because this version of oracle somehow dont work.


      thsi is my code.

      CREATE OR REPLACE PACKAGE PCK_UDA1
      IS
      type arreglo is table of rowid index by binary_integer;
      v_u1acct arreglo;
      v_reset arreglo;

      END;
      /


      CREATE OR REPLACE TRIGGER RESET_UDA1_BU
      BEFORE UPDATE ON UDA1
      BEGIN
      DBMS_OUTPUT.put_line('RESET_UDA1_BU');
      PCK_UDA1.v_u1acct := PCK_UDA1.v_reset;
      END;
      /

      here there are the condition to update the status
      CREATE OR REPLACE TRIGGER UDA1_AU
      AFTER UPDATE ON UDA1 FOR EACH ROW
      BEGIN
      DBMS_OUTPUT.put_line('UDA1_AU');

      IF ((:NEW.U1STATUS = 'VEN' OR :NEW.U1STATUS = 'SAC') AND :OLD.U1STATUS = 'LIQ') THEN
      PCK_UDA1.v_u1acct(PCK_UDA1.v_u1acct.count+1):= :NEW.u1acct;

      END IF;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN dbms_output.put_line('SIN DATOS');
      WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
      END;
      /

      CREATE OR REPLACE TRIGGER UPDATE_UDA1_FIN2
      AFTER UPDATE ON UDA1
      BEGIN
      DBMS_OUTPUT.put_line('UPDATE_UDA1_FIN2');

      FOR i in 1 .. PCK_UDA1.v_u1acct.count loop
      UPDATE UDa1 set u1calc5= (SELECT u1calc5 FROM GESTION.HIS_FINANCIERA2 where dmacct = PCK_UDA1.v_u1acct(i)),

      WHERE u1acct = PCK_UDA1.v_u1acct(i);
      END LOOP;
      PCK_UDA1.v_u1acct.delete;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN dbms_output.put_line('SIN DATOS');
      WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
      END;
      /

      here i update the values of the other table.CREATE OR REPLACE TRIGGER UPDATE_DLQ_FIN2
      AFTER UPDATE ON UDA1
      FOLLOWS UPDATE_UDA1_FIN2
      BEGIN
      DBMS_OUTPUT.put_line('UPDATE_DLQ_FIN2');
      FOR j in 1 .. PCK_UDA1.v_u1acct.count loop
      UPDATE DELQMST SET DMAMTDLQ =(SELECT DMAMTDLQ FROM GESTION.HIS_FINANCIERA2 where dmacct = PCK_UDA1.v_u1acct(j)),
      DMCURBAL =(SELECT DMCURBAL FROM GESTION.HIS_FINANCIERA2 where dmacct = PCK_UDA1.v_u1acct(j))
      WHERE DMACCT = PCK_UDA1.v_u1acct(j);
      END LOOP;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN dbms_output.put_line('SIN DATOS');
      WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
      END;
      /

      HEres is the one that i want to delete the table from i get the values.CREATE OR REPLACE TRIGGER PARENT_DELETE_FIN2
      AFTER UPDATE ON UDA1
      FOLLOWS UPDATE_DLQ_FIN2
      BEGIN
           DBMS_OUTPUT.put_line('PARENT_DELETE_FIN2');
      FOR k in 1 .. PCK_UDA1.v_u1acct.count loop
      DELETE FROM GESTION.HIS_FINANCIERA2 where dmacct = PCK_UDA1.v_u1acct(k);
      end loop;
           EXCEPTION
      WHEN NO_DATA_FOUND THEN dbms_output.put_line('SIN DATOS');
      WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
      END;
      /

      is there a wayr to tell oracle to update first the table the 2 table, and then delete the values of the third one.