mezlan-magento-category-product-association-trigger.sql

create table trigger_execution_log (
    id int not null auto_increment,
    ran_at timestamp default current_timestamp,
    trigger_name varchar(255) not null,
    message varchar(255),
    primary key (id)
);

DELIMITER $$

DROP FUNCTION IF EXISTS UpdateProductEntityCategoryIds$$

CREATE FUNCTION UpdateProductEntityCategoryIds(updated_product_id INT)
RETURNS TEXT
BEGIN
    SET @new_category_ids = (
        SELECT GROUP_CONCAT(category_id)
        FROM ml_catalog_category_product
        WHERE product_id = updated_product_id
    );
    SET @old_category_ids = (
        SELECT category_ids
        FROM ml_catalog_product_entity
        WHERE entity_id = updated_product_id
    );
    IF @new_category_ids != @old_category_ids THEN
        UPDATE ml_catalog_product_entity
        SET category_ids = @new_category_ids
        WHERE entity_id = updated_product_id;
        RETURN CONCAT(
            'Product ID ',
            updated_product_id,
            ' category_ids changed from "',
            @old_category_ids,
            '" set to "',
            @new_category_ids, '"'
        );
    END IF;
    RETURN 'No update required';
END$$

DROP TRIGGER IF EXISTS category_product_update$$

CREATE TRIGGER category_product_update
AFTER UPDATE ON ml_catalog_category_product
FOR EACH ROW BEGIN
    DECLARE return_value TEXT;
    SELECT UpdateProductEntityCategoryIds(NEW.product_id) INTO return_value;
    INSERT INTO trigger_execution_log (trigger_name, message) VALUES ('category_product_update', return_value);
END$$

DROP TRIGGER IF EXISTS category_product_insert$$

CREATE TRIGGER category_product_insert
AFTER INSERT ON ml_catalog_category_product
FOR EACH ROW BEGIN
    DECLARE return_value TEXT;
    SELECT UpdateProductEntityCategoryIds(NEW.product_id) INTO return_value;
    INSERT INTO trigger_execution_log (trigger_name, message) VALUES ('category_product_insert', return_value);
END$$

DROP TRIGGER IF EXISTS category_product_delete$$

CREATE TRIGGER category_product_delete
AFTER DELETE ON ml_catalog_category_product
FOR EACH ROW BEGIN
    DECLARE return_value TEXT;
    SELECT UpdateProductEntityCategoryIds(OLD.product_id) INTO return_value;
    INSERT INTO trigger_execution_log (trigger_name, message) VALUES ('category_product_delete', return_value);
END$$

-- DROP FUNCTION IF EXISTS UpdateCategoryProductAssociation$$

-- CREATE FUNCTION UpdateCategoryProductAssociation(updated_product_id INT)
-- RETURNS TEXT
-- BEGIN
--     INSERT IGNORE INTO ml_catalog_category_product (product_id, category_id, position)
--     SELECT
--         product.entity_id
--         , SUBSTRING_INDEX(SUBSTRING_INDEX(product.category_ids, ',', numbers.n), ',', -1) category_id
--         , 98989
--     FROM (
--         SELECT 1 n UNION ALL
--         SELECT 2 UNION ALL SELECT 3 UNION ALL
--         SELECT 4 UNION ALL SELECT 5
--     ) numbers
--     INNER JOIN ml_catalog_product_entity product ON CHAR_LENGTH(product.category_ids) - CHAR_LENGTH(REPLACE(product.category_ids, ',', '')) >= numbers.n - 1
--     WHERE entity_id = updated_product_id;
--     SET @affected_rows = (SELECT ROW_COUNT());
--     RETURN CONCAT('Updated ', @affected_rows, ' rows');
-- END$$

-- DROP TRIGGER IF EXISTS product_update$$

-- CREATE TRIGGER product_update
-- AFTER UPDATE ON ml_catalog_product_entity
-- FOR EACH ROW BEGIN
--     DECLARE return_value TEXT;
--     SELECT UpdateCategoryProductAssociation(NEW.entity_id) INTO return_value;
-- END$$

-- DROP TRIGGER IF EXISTS product_insert$$

-- CREATE TRIGGER product_insert
-- AFTER INSERT ON ml_catalog_product_entity
-- FOR EACH ROW BEGIN
--     DECLARE return_value TEXT;
--     SELECT UpdateCategoryProductAssociation(NEW.entity_id) INTO return_value;
-- END$$

-- DROP TRIGGER IF EXISTS product_delete$$

-- CREATE TRIGGER product_delete
-- AFTER DELETE ON ml_catalog_product_entity
-- FOR EACH ROW BEGIN
--     DECLARE return_value TEXT;
--     SELECT UpdateCategoryProductAssociation(OLD.entity_id) INTO return_value;
-- END$$

DELIMITER ;