DROP PROCEDURE IF EXISTS add_product_flat_in_change_log;;
DROP TRIGGER IF EXISTS after_products_flat_update;;
DROP TRIGGER IF EXISTS after_products_flat_insert;;

DROP PROCEDURE IF EXISTS add_product_in_change_log;;
CREATE PROCEDURE add_product_in_change_log(IN updated_product_id INT(11))
READS SQL DATA
BEGIN
    INSERT INTO products_change_log
        SET product_id = updated_product_id
        ON DUPLICATE KEY UPDATE product_id = updated_product_id;
    INSERT INTO products_ecp_change_log
        SET product_id = updated_product_id
        ON DUPLICATE KEY UPDATE product_id = updated_product_id;
END;;


DROP TRIGGER IF EXISTS after_product_variants_update;;
CREATE TRIGGER after_product_variants_update
    AFTER UPDATE ON product_variants
    FOR EACH ROW 
BEGIN
    CALL add_product_in_change_log(NEW.product_id);
END;;

DROP TRIGGER IF EXISTS after_product_variants_insert;;
CREATE TRIGGER after_product_variants_insert 
    AFTER INSERT ON product_variants
    FOR EACH ROW 
BEGIN
    CALL add_product_in_change_log(NEW.product_id);
END;;
