6 Replies Latest reply on Jun 24, 2010 10:37 AM by 277993

    After Update Trigger executes twice when single row is uptd thro proc

    781021
      We have the below trigger in our db. When a single record is updated using a procedure the trigger is executed twice and it inserts two records in other table.
      But when i issue an update statement using any sql client tool it is executing only once and inserts only one record in other table.

      Can any one please help me to find the reason?

      Trigger:*

      create or replace TRIGGER CX_HEADER_ESCL_T1 AFTER UPDATE OF STATUS ON CX_HEADER
      FOR EACH ROW
      DECLARE
      "b1-CTRIYJ" boolean := FALSE;
      BEGIN
      IF UPDATING('STATUS') AND(:NEW.status = 'SUCCESS') THEN
      "b1-CTRIYJ" := TRUE;
      END IF;

      IF "b1-CTRIYJ" = TRUE THEN
      INSERT
      INTO siebel.s_escl_req(req_id, created, bt_row_id, rule_id, tbl_name, created_by, group_id)
      VALUES('11111111', CURRENT_DATE, :NEW.row_id, '1-CTRIYJ', 'CX_HEADER', :NEW.last_upd_by, '1-2CU3');
      "b1-CTRIYJ" := FALSE;
      END IF;

      END;


      Procedure:

      CREATE OR REPLACE
      PROCEDURE CLOSE_BATCH
      (ChildRecordCount IN NUMBER, HeaderId IN VARCHAR2, CompletionStatus OUT VARCHAR2) AS
      CafeChildCount NUMBER;

      BEGIN
      select count(*) into CafeChildCount from SIEBEL.CX_CHILD where HEADER_ID=HeaderId;
      IF ChildRecordCount = CafeChildCount THEN
      update SIEBEL.CX_HEADER set STATUS ='SUCCESS', MODIFICATION_NUM = MODIFICATION_NUM+1 where HEADER_ID=HeaderId;
      CompletionStatus := 'SUCCESS';
      ELSE
      update SIEBEL.CX_CHILD set STATUS='FAILED' where HEADER_ID=HeaderId;
      update SIEBEL.CX_HEADER set STATUS='FAILED' where HEADER_ID=HeaderId;
      CompletionStatus := 'FAILED';
      END IF;
      commit;
      /*CompletionStatus := 'SUCCESS';*/
      EXCEPTION
      WHEN OTHERS THEN
      CompletionStatus := SQLCODE;
      rollback;
      END;