Created
April 26, 2023 13:57
-
-
Save RomanTuras/187ea5c138e1d3a332114cbbf4900abd to your computer and use it in GitHub Desktop.
SQL trigger example
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Trigger for `oc_product_to_category` | |
| -- 1) IF INSERTED a new row with main_category = 1 | |
| -- 2) IF UPDATED with: new.main_category = 1 | |
| DELIMITER $$ | |
| DROP TRIGGER IF EXISTS update_product_trigger $$ | |
| CREATE TRIGGER update_product_trigger AFTER UPDATE ON oc_product_to_category FOR EACH ROW BEGIN | |
| IF NOT EXISTS(SELECT * FROM oc_version_changes WHERE entity_id=new.product_id AND entity_type='product') AND new.main_category = 1 THEN | |
| INSERT INTO oc_version_changes (entity_id,entity_type) VALUES (new.product_id, 'product'); END IF; END$$ | |
| DELIMITER ; | |
| DELIMITER $$ | |
| DROP TRIGGER IF EXISTS insert_product_trigger $$ | |
| CREATE TRIGGER insert_product_trigger AFTER INSERT ON oc_product_to_category FOR EACH ROW BEGIN | |
| IF new.main_category = 1 | |
| THEN INSERT INTO oc_version_changes (entity_id,entity_type) VALUES (new.product_id, 'product'); | |
| END IF; | |
| END$$ | |
| DELIMITER ; | |
| -- Trigger for `oc_category` | |
| -- 1) IF INSERTED a new row for oc_category | |
| -- 2) IF UPDATED with: new.parent_id <> old.parent_id | |
| -- 3) If INSERTED a new category THEN generate UUID for it | |
| DELIMITER $$ | |
| DROP TRIGGER IF EXISTS insert_category_trigger $$ | |
| CREATE TRIGGER insert_category_trigger AFTER INSERT ON oc_category FOR EACH ROW BEGIN | |
| INSERT INTO oc_version_changes (entity_id,entity_type) VALUES (new.category_id, 'category'); | |
| INSERT INTO oc_category_to_1c (category_id,1c_id) VALUES (new.category_id, uuid()); | |
| END$$ | |
| DELIMITER ; | |
| DELIMITER $$ | |
| DROP TRIGGER IF EXISTS update_category_trigger $$ | |
| CREATE TRIGGER update_category_trigger AFTER UPDATE ON oc_category FOR EACH ROW BEGIN | |
| IF NOT EXISTS(SELECT * FROM oc_version_changes WHERE entity_id=new.product_id AND entity_type='category') AND new.parent_id <> old.parent_id THEN | |
| INSERT INTO oc_version_changes (entity_id,entity_type) VALUES (new.category_id, 'category'); END IF; END$$ | |
| DELIMITER ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment