CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Kiffer Van
Kiffer Van

Reputation: 242

Update Trigger not firing in oracle

Hi i have this existing trigger and i'm quete new at this so in my trigeer i have to capture the insert, update and delete but seems like the only working is the inserting.

CREATE OR REPLACE TRIGGER ATCB_TCM_SAP."TRI_PM_TBLIF240"
AFTER INSERT ON ATCB_TCM_SAP.PM_TBLIF240_TMP FOR EACH ROW
DECLARE
 updating_key_fields EXCEPTION;
BEGIN
IF INSERTING THEN
  INSERT INTO PM_TBLIF240 VALUES (
  :NEW.MSGID,
  :NEW.WERKS,
  :NEW.EQUNR,
  :NEW.MPTYPE,
  :NEW.DATE_,
  :NEW.TIME,
  :NEW.RECDC,
  :NEW.IDIFF,
  :NEW.READR,
  :NEW.IIND,
  :NEW.QMART
  );
END IF;
IF UPDATING THEN
        IF UPDATING('MSGID') THEN
            RAISE updating_key_fields;
        END IF;
  INSERT INTO PM_TBLIF240 VALUES (
  :OLD.MSGID,
  :OLD.WERKS,
  :OLD.EQUNR,
  :OLD.QMART,
  :OLD.MPTYPE,
  :OLD.RECDC,
  :OLD.IDIFF,
  :OLD.READR,
  :OLD.IIND,
  :OLD.DATE_,
  :OLD.TIME
  );
END IF;
IF DELETING THEN
 INSERT INTO PM_TBLIF240 VALUES (
  :OLD.MSGID,
  :OLD.WERKS,
  :OLD.EQUNR,
  :OLD.QMART,
  :OLD.MPTYPE,
  :OLD.RECDC,
  :OLD.IDIFF,
  :OLD.READR,
  :OLD.IIND,
  :OLD.DATE_,
  :OLD.TIME
  );
END IF;
 EXCEPTION
        WHEN updating_key_fields THEN
            raise_application_error(-20300, 'ActiveDB Error: cannot update key fields of source table.');
END;

i've try this on uodating 

IF UPDATING THEN
        IF UPDATING('MSGID') THEN
            RAISE updating_key_fields;
        END IF;
  INSERT INTO PM_TBLIF240 VALUES (
  :OLD.MSGID,
  :NEW.WERKS,
  :NEW.EQUNR,
  :NEW.MPTYPE,
  :NEW.DATE_,
  :NEW.TIME,
  :NEW.RECDC,
  :NEW.IDIFF,
  :NEW.READR,
  :NEW.IIND,
  :NEW.QMART
  );
END IF;

but still no luck . I try to uodate the time in table 1 but but no effect on table 2. Hope someone help me out with this.

Upvotes: 0

Views: 1585

Answers (2)

Ergi Nushi
Ergi Nushi

Reputation: 863

As suggested, you should check the second line. You are saying that the trigger should be executed only after INSERT.

Change it to:

CREATE OR REPLACE TRIGGER atcb_tcm_sap."TRI_PM_TBLIF240" BEFORE
    DELETE OR INSERT OR UPDATE ON atcb_tcm_sap.pm_tblif240_tmp --> this line here
    FOR EACH ROW
DECLARE
    updating_key_fields EXCEPTION;
BEGIN
    IF inserting THEN
        INSERT INTO pm_tblif240 VALUES (
            :new.msgid,
            :new.werks,
            :new.equnr,
            :new.mptype,
            :new.date_,
            :new.time,
            :new.recdc,
            :new.idiff,
            :new.readr,
            :new.iind,
            :new.qmart
        );

    END IF;

    IF updating THEN
        IF updating('MSGID') THEN
            RAISE updating_key_fields;
        END IF;
        INSERT INTO pm_tblif240 VALUES (
            :old.msgid,
            :old.werks,
            :old.equnr,
            :old.qmart,
            :old.mptype,
            :old.recdc,
            :old.idiff,
            :old.readr,
            :old.iind,
            :old.date_,
            :old.time
        );

    END IF;

    IF deleting THEN
        INSERT INTO pm_tblif240 VALUES (
            :old.msgid,
            :old.werks,
            :old.equnr,
            :old.qmart,
            :old.mptype,
            :old.recdc,
            :old.idiff,
            :old.readr,
            :old.iind,
            :old.date_,
            :old.time
        );

    END IF;

EXCEPTION
    WHEN updating_key_fields THEN
        raise_application_error(-20300, 'ActiveDB Error: cannot update key fields of source table.');
END;

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

Look at that second line:

CREATE OR REPLACE TRIGGER ATCB_TCM_SAP."TRI_PM_TBLIF240"
AFTER **INSERT** ON ATCB_TCM_SAP.PM_TBLIF240_TMP FOR EACH ROW

Upvotes: 2

Related Questions