------------------------
-- ARTICLE_COLOR_LOOKUP
------------------------

CREATE OR REPLACE TRIGGER AI_ACL
AFTER INSERT ON ARTICLE_COLOR_LOOKUP
FOR EACH ROW
BEGIN
    INSERT INTO
        EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC
    VALUES
    (:NEW.ARTICLE_CODE,
     :NEW.COLOR_CODE,
     :NEW.ARTICLE_LABEL,
     :NEW.COLOR_LABEL,
     :NEW.CATEGORY,
     :NEW.SALE_PRICE,
     :NEW.FAMILY_NAME,
     :NEW.FAMILY_CODE,
     'I'
    );
END;
/

CREATE OR REPLACE TRIGGER AU_ACL
AFTER UPDATE ON ARTICLE_COLOR_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_COLOR_LOOKUP_EMODEINC
    WHERE
        :NEW.ARTICLE_CODE = EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC.ARTICLE_CODE
    AND
        :NEW.COLOR_CODE = EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC.COLOR_CODE;


    IF(nb <> 0) THEN

        -- savoir si la ligne existe en insert alors on modifie la ligne en conservant insert
        SELECT
            operation_type INTO ope_type
        FROM
            EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC
        WHERE
            :NEW.ARTICLE_CODE = EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC.ARTICLE_CODE
        AND
            :NEW.COLOR_CODE = EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC.COLOR_CODE;

        IF(ope_type = 'I') THEN
            UPDATE
                EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC
            SET
                ARTICLE_LABEL =:NEW.ARTICLE_LABEL,
                COLOR_LABEL =:NEW.COLOR_LABEL,
                CATEGORY =:NEW.CATEGORY,
                SALE_PRICE =:NEW.SALE_PRICE,
                FAMILY_NAME =:NEW.FAMILY_NAME,
                FAMILY_CODE =:NEW.FAMILY_CODE,
                OPERATION_TYPE = 'I'
            WHERE
                ARTICLE_CODE = :NEW.ARTICLE_CODE
            AND
                COLOR_CODE = :NEW.COLOR_CODE;
        ELSE
            UPDATE
                EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC
            SET
                ARTICLE_LABEL =:NEW.ARTICLE_LABEL,
                COLOR_LABEL =:NEW.COLOR_LABEL,
                CATEGORY =:NEW.CATEGORY,
                SALE_PRICE =:NEW.SALE_PRICE,
                FAMILY_NAME =:NEW.FAMILY_NAME,
                FAMILY_CODE =:NEW.FAMILY_CODE,
                OPERATION_TYPE = 'U'
            WHERE
                ARTICLE_CODE = :NEW.ARTICLE_CODE
            AND
                COLOR_CODE = :NEW.COLOR_CODE;
        END IF;

    ELSE
        INSERT INTO
            EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC
        VALUES
        (:NEW.ARTICLE_CODE,
         :NEW.COLOR_CODE,
         :NEW.ARTICLE_LABEL,
         :NEW.COLOR_LABEL,
         :NEW.CATEGORY,
         :NEW.SALE_PRICE,
         :NEW.FAMILY_NAME,
         :NEW.FAMILY_CODE,
         'U'
        );
    END IF;
END;
/

CREATE OR REPLACE TRIGGER AD_ACL
AFTER DELETE ON ARTICLE_COLOR_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_COLOR_LOOKUP_EMODEINC
    WHERE
        :OLD.ARTICLE_CODE = EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC.ARTICLE_CODE
    AND
        :OLD.COLOR_CODE = EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC.COLOR_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_COLOR_LOOKUP_EMODEINC
        WHERE
            :OLD.ARTICLE_CODE = EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC.ARTICLE_CODE
        AND
            :OLD.COLOR_CODE = EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC.COLOR_CODE;

        IF(ope_type = 'I') THEN
            DELETE FROM
                EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC
            WHERE
                ARTICLE_CODE = :OLD.ARTICLE_CODE
            AND
                COLOR_CODE = :OLD.COLOR_CODE;
        ELSE
            UPDATE
                EMODE_INC.ARTICLE_COLOR_LOOKUP_EMODEINC
            SET
                ARTICLE_LABEL =:OLD.ARTICLE_LABEL,
                COLOR_LABEL =:OLD.COLOR_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
            AND
                COLOR_CODE = :OLD.COLOR_CODE;
        END IF;

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