can i create a multi event single trigger like we create in Oracle (create or replace trigger ... before Insert or Update)
if yes, please help / guide with a complete example.
i am trying to create a trigger as below but it is throwing error. trying to use this trigger to:
1) get sequence next value in ID column because i have to use value of ID column ( SET vparent_path := CONCAT(vparent_path, NEW.id, '/'); )
2) want to create values of 3 columns from parent_id
code is working fine if: (a) its only an Insert trigger (b) if i do not use NEW.id -but i have to- or any alternate solution to have ID column value within this trigger and i can avoid using a sequence.
- Code: Select all
DELIMITER $$
CREATE OR REPLACE
TRIGGER `xairy`.`set_parent` BEFORE INSERT, UPDATE
ON `xairy`.`categories`
FOR EACH ROW BEGIN
DECLARE vcomplete_name, vparent_path VARCHAR(100);
DECLARE v_id, vcategory_level, vbase_category, vparent_id INT;
IF NEW.id IS NULL THEN
SELECT NEXT VALUE FOR seq_category_id INTO v_id;
END IF;
IF NEW.parent_id IS NOT NULL THEN
SELECT complete_name, parent_path, category_level, 0 base_category
INTO vcomplete_name, vparent_path, vcategory_level, vbase_category
FROM categories
WHERE id = NEW.parent_id;
--
SET vcomplete_name = CONCAT(vcomplete_name,' / ',NEW.category_name);
SET vcategory_level := nvl(vcategory_level,0)+1;
--
IF vcategory_level = 0 THEN
SET vparent_path := CONCAT(NEW.parent_id, '/');
ELSE
SET vparent_path := CONCAT(vparent_path, v_id, '/');
END IF;
--
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(vparent_path, '/', 1), '/', -1)
INTO vbase_category
FROM DUAL;
SET NEW.complete_name := vcomplete_name;
SET NEW.parent_path := vparent_path;
SET NEW.category_level := vcategory_level;
SET NEW.base_category := vbase_category;
ELSE
SELECT NVL(MAX(id),0)+1 INTO vparent_id FROM categories;
SET NEW.complete_name := NEW.category_name;
SET NEW.parent_path := CONCAT(vparent_id, '/');
SET NEW.category_level := 0;
SET NEW.base_category := 0;
END IF;
END$$
DELIMITER ;
regards.