Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment