Note

Issue Where Statement Trigger Does Not Execute During Merge Statement Execution

-- #Main Table
CREATE TABLE E01_SITE_SUPPLIER_ITEM_VER (
    SITE_CODE        VARCHAR2(10),
    AFFILIATE_CODE   VARCHAR2(10),
    SUPPLIER_CODE    VARCHAR2(10),
    ITEM_CODE        VARCHAR2(10),
    APPROVE_DATETIME DATE
);

-- #Result Table
CREATE TABLE EAI_E01_ITEM_APPROVE (
    EAI_ROW_SEQ      NUMBER,
    SITE_CODE        VARCHAR2(10),
    AFFILIATE_CODE   VARCHAR2(10),
    SUPPLIER_CODE    VARCHAR2(10),
    ITEM_CODE        VARCHAR2(10),
    ITEM_VER_SEQ     NUMBER,
    STATUS_DATE      VARCHAR2(8),
    STATUS_TIME      VARCHAR2(6),
    STATUS_CODE      VARCHAR2(10),
    STATUS_NAME      VARCHAR2(50),
    SEND_STATUS_CODE VARCHAR2(1)
);


-- #Sequence
CREATE SEQUENCE EAI_E01_ITEM_APPROVE_SEQ START WITH 1 INCREMENT BY 1;


-- #Package
CREATE OR REPLACE PACKAGE PKG_SUPPLIER_ITEM AS
    TYPE supplier_item_rec IS RECORD (
        SITE_CODE      VARCHAR2(10),
        AFFILIATE_CODE VARCHAR2(10),
        SUPPLIER_CODE  VARCHAR2(10),
        ITEM_CODE      VARCHAR2(10)
    );


TYPE supplier_item_tab IS TABLE OF supplier_item_rec INDEX BY PLS_INTEGER;

v_supplier_item supplier_item_tab;

PROCEDURE SP_ADD_SUPPLIER_ITEM(
    vSiteCode      VARCHAR2,
    vAffiliateCode VARCHAR2,
    vSupplierCode  VARCHAR2,
    vItemCode      VARCHAR2
);

PROCEDURE SP_UPDATE_SUPPLIER;

END PKG_SUPPLIER_ITEM;
/


CREATE OR REPLACE PACKAGE BODY PKG_SUPPLIER_ITEM AS
    PROCEDURE SP_ADD_SUPPLIER_ITEM(
        vSiteCode      VARCHAR2,
        vAffiliateCode VARCHAR2,
        vSupplierCode  VARCHAR2,
        vItemCode      VARCHAR2
    ) IS
        idNum PLS_INTEGER;
    BEGIN
        idNum := NVL(v_supplier_item.LAST, 0) + 1;
        v_supplier_item(idNum).SITE_CODE      := vSiteCode;
        v_supplier_item(idNum).AFFILIATE_CODE := vAffiliateCode;
        v_supplier_item(idNum).SUPPLIER_CODE  := vSupplierCode;
        v_supplier_item(idNum).ITEM_CODE      := vItemCode;


END SP_ADD_SUPPLIER_ITEM;

PROCEDURE SP_UPDATE_SUPPLIER IS
    v_index PLS_INTEGER;
BEGIN
    v_index := v_supplier_item.FIRST;

    WHILE v_index IS NOT NULL LOOP
        INSERT INTO EAI_E01_ITEM_APPROVE (
            EAI_ROW_SEQ, SITE_CODE, AFFILIATE_CODE, SUPPLIER_CODE, ITEM_CODE
        ) VALUES (
            EAI_E01_ITEM_APPROVE_SEQ.NEXTVAL,
            v_supplier_item(v_index).SITE_CODE,
            v_supplier_item(v_index).AFFILIATE_CODE,
            v_supplier_item(v_index).SUPPLIER_CODE,
            v_supplier_item(v_index).ITEM_CODE
        );

        v_index := v_supplier_item.NEXT(v_index);

    END LOOP;

    v_supplier_item.DELETE;

END SP_UPDATE_SUPPLIER;

END PKG_SUPPLIER_ITEM;
/


-- #Row Trigger
CREATE OR REPLACE TRIGGER TRG_E01_SUPPLIER_ITEM_VER_ROW
AFTER INSERT ON E01_SITE_SUPPLIER_ITEM_VER
FOR EACH ROW
BEGIN
    PKG_SUPPLIER_ITEM.SP_ADD_SUPPLIER_ITEM(
        :NEW.SITE_CODE,
        :NEW.AFFILIATE_CODE,
        :NEW.SUPPLIER_CODE,
        :NEW.ITEM_CODE
    );
END;
/


-- #Statement Trigger
CREATE OR REPLACE TRIGGER TRG_E01_SUPPLIER_ITEM_VER_STMT
AFTER INSERT ON E01_SITE_SUPPLIER_ITEM_VER
BEGIN
    PKG_SUPPLIER_ITEM.SP_UPDATE_SUPPLIER;
END;
/


-- #Delete existing data if any
DELETE FROM E01_SITE_SUPPLIER_ITEM_VER;


delete from EAI_E01_ITEM_APPROVE;


COMMIT;


-- #Executing merge statement
MERGE INTO E01_SITE_SUPPLIER_ITEM_VER t
USING (SELECT 'S001' SITE_CODE, 'A001' AFFILIATE_CODE, 'SUP001' SUPPLIER_CODE, 'ITM001' ITEM_CODE FROM DUAL) s
ON (t.SITE_CODE = s.SITE_CODE AND t.AFFILIATE_CODE = s.AFFILIATE_CODE AND t.ITEM_CODE = s.ITEM_CODE)
WHEN NOT MATCHED THEN
    INSERT (SITE_CODE, AFFILIATE_CODE, SUPPLIER_CODE, ITEM_CODE, APPROVE_DATETIME)
    VALUES (s.SITE_CODE, s.AFFILIATE_CODE, s.SUPPLIER_CODE, s.ITEM_CODE, SYSDATE);


1 row merged. - Executed successfully without error


-- #Check results after executing merge statement


SELECT * FROM EAI_E01_ITEM_APPROVE;


0 rows selected. - No results


-- #Execute insert statement instead of merge
INSERT into E01_SITE_SUPPLIER_ITEM_VER (SITE_CODE, AFFILIATE_CODE, SUPPLIER_CODE, ITEM_CODE, APPROVE_DATETIME)
    VALUES ('a', 'b', 'c', 'd', sysdate);


1 rows inserted. - Executed successfully without error


-- #Check results after insert statement execution
SELECT * FROM EAI_E01_ITEM_APPROVE;


EAI_ROW_SEQ SITE_CODE  AFFILIATE_CODE SUPPLIER_CODE ITEM_CODE  ITEM_VER_SEQ



STATUS_DATE STATUS_TIME STATUS_CODE



STATUS_NAME                                        SEND_STATUS_CODE



      8 S001       A001           SUP001        ITM001
      9 a          b              c             d