CREATE OR REPLACE TRIGGER AD_AL
AFTER DELETE ON ARTICLE_LOOKUP
FOR EACH ROW
DECLARE
    nb INTEGER;
    ope_type CHAR;
BEGIN

    -- Si ligne déjà existante dans la table emode_inc
    SELECT
        COUNT(*) INTO nb
    FROM
        EMODE_INC.ARTICLE_LOOKUP_EMODEINC
    WHERE
        :OLD.ARTICLE_CODE = EMODE_INC.ARTICLE_LOOKUP_EMODEINC.ARTICLE_CODE;


    IF(nb <> 0) THEN

        -- savoir si la ligne existe en insert alors on supprime la ligne
        SELECT
            operation_type INTO ope_type
        FROM
            EMODE_INC.ARTICLE_LOOKUP_EMODEINC
        WHERE
            :OLD.ARTICLE_CODE = EMODE_INC.ARTICLE_LOOKUP_EMODEINC.ARTICLE_CODE;

        IF(ope_type = 'I') THEN
            DELETE FROM
                EMODE_INC.ARTICLE_LOOKUP_EMODEINC
            WHERE
                ARTICLE_CODE = :OLD.ARTICLE_CODE;
        ELSE
            UPDATE
                EMODE_INC.ARTICLE_LOOKUP_EMODEINC
            SET
                ARTICLE_LABEL =:OLD.ARTICLE_LABEL,
                CATEGORY =:OLD.CATEGORY,
                SALE_PRICE =:OLD.SALE_PRICE,
                FAMILY_NAME =:OLD.FAMILY_NAME,
                FAMILY_CODE =:OLD.FAMILY_CODE,
                OPERATION_TYPE = 'D'
            WHERE
                ARTICLE_CODE = :OLD.ARTICLE_CODE;
        END IF;

    ELSE
        INSERT INTO
            EMODE_INC.ARTICLE_LOOKUP_EMODEINC
        VALUES
        (:OLD.ARTICLE_CODE,
         :OLD.ARTICLE_LABEL,
         :OLD.CATEGORY,
         :OLD.SALE_PRICE,
         :OLD.FAMILY_NAME,
         :OLD.FAMILY_CODE,
         'D'
        );
    END IF;
END;
/