mariadb: multi event triggers

[AhmedHaroon]

i am using XAMPP ver 3.3.0 on Windows 10. i am new to mariadb/mysql and trying to explore and want help from experts here.
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.

[Nobbie]

You cannot create a single trigger for AFTER / BEFORE INSERT or any other combination. A trigger must be qualified for one certain operation, either BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE.

The syntax is:

CREATE TRIGGER blabla
AFTER INSERT ON something ...

or similar.

See https://mariadb.com/kb/en/trigger-overv ... -a-trigger

You CANNOT CREATE TRIGGER like:

CREATE TRIGGER blabla
AFTER OR BEFORE INSERT ON something ...

and you also cannot leave out the AFTER / BEFORE event (what you actually did in your example). I dont know your application and your application logic, probably you have to create more than one trigger for your needs. I dont know. You may also simply use "REPLACE" instead of "CREATE OR REPLACE", as MariaDB/MySQL automatically does an INSERT if the row is unknown (due to Primary Key) or a REPLACE if there is already a matching row. See https://mariadb.com/kb/en/replace/

If you use PHP or similar language, you may also use standard Select / Insert / Replace SQL Statements without triggers and perform all necessary data manipulation in PHP. There is no need to use triggers.

Last not least, i think you simply can create two triggers (doing both the same) like:

CREATE TRIGGER tr1
AFTER INSERT ON ...
... action ...
and

CREATE TRIGGER tr2
AFTER UPDATE ON ...
... action ....

And when you run a REPLACE, mariadb executes either tr1 or tr2, dependig on the action it did, either INSERT or UPDATE. It never will execute BOTH triggers, as this is physically impossible. Simply provide the same action to tr1 and tr2 and you are done.