IF statement is not accepted in MySQL trigger, issuing Code 1064 but no reference into the statement

Question

I'm using MariaDB (10.3.29-MariaDB-0+deb10u1) and DbVisualizer (Pro 12.1.1 [Build #3237]) as an IDE.

I want to manage the primary key of a table by having MariaDB set the key to a UUID, I started with this

CREATE TRIGGER  before_pkey_maintenance
BEFORE INSERT ON maintenance
    FOR EACH ROW
       SET NEW.pkey = uuid();

which worked perfectly, except that I started providing my own UUIDs, which the trigger over-wrote, of course. I tried the following to create a UUID only if one was not provided:

CREATE TRIGGER  before_pkey_maintenance
BEFORE INSERT ON maintenance
    FOR EACH ROW
        IF NEW.pkey IS NULL THEN
           SET NEW.pkey = uuid();
        END IF;

The problem is, when I execute the create, I get the following error:

[Code: 1064, SQL State: 42000]  (conn=1002) You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax
to use near '' at line 5  [Script position: 132 - 168]

Error 1064 is documented (https://riptutorial.com/mysql/example/2995/error-code-1064--syntax-error) as not correctly using back-ticks, which I can deal with ... except that the message is referring to "syntax to use near '' at line 5", the empty string in the message is less than helpful.

I found found MySQL Trigger with IF statement returning #1064, which adds the use of "delimiter" and tried "|", "//", and "$$" as delimiters. I've tried MANY variations, all similar to the following:

delimiter $$

CREATE TRIGGER  before_pkey_maintenance
    BEFORE INSERT ON maintenance
        FOR EACH ROW
            IF NEW.pkey IS null THEN
               SET NEW.pkey = uuid();
            END IF;
END
$$
delimiter ;

... but none of them worked.

I've looked through DbVisualizer's docs and found nothing that indicates it performs "magic" behind the scenes, but I suspect that if any of the above is correct that DbVis is executing these separately.

Answer 1

Basically You have nor error. Copy the code from the example and use it and you will get any error anymore, you might have some kind of invisible character so delete every thing. and use theis code

CREATE tABLE maintenance (pkey VARCHAR(36))

CREATE TRIGGER  before_pkey_maintenance
    BEFORE INSERT ON maintenance
        FOR EACH ROW
            IF NEW.pkey IS null THEN
               SET NEW.pkey = uuid();
            END IF;
END

INSERT INTO maintenance VALUES (NULL)

✓

SELECT * FROM maintenance

| pkey                                 |
| :----------------------------------- |
| c973e92c-3a6f-11ec-93bc-00163e55bd17 |

db<>fiddle here

here is a Version with DELIMITER

delimiter $$

CREATE TRIGGER  before_pkey_maintenance
    BEFORE INSERT ON maintenance
        FOR EACH ROW
            IF NEW.pkey IS null THEN
               SET NEW.pkey = uuid();
            END IF;
END
$$
delimiter ;

Answer 2

I found this https://mariadb.com/kb/en/trigger-keeps-creating-error/, which led to my solution:

CREATE TRIGGER  before_pkey_maintenance
BEFORE INSERT ON maintenance
FOR EACH ROW
    SET new.pkey = IF (NEW.pkey is null or NEW.pkey = '', uuid(), NEW.pkey);

Even though this works for this situation, it is not a complete answer because it is not suitable if the trigger needs to execute more complex code.

Answer 3

This is, in fact, a "feature" of dbvisualizer.

The page https://confluence.dbvis.com/display/UG121/Executing+Complex+Statements describes how to deal with it, the key point is added an '@' to the delimiter statement and the semicolon:

@delimiter $$;

CREATE TRIGGER  before_pkey_maintenance
    BEFORE INSERT ON maintenance
        FOR EACH ROW
            IF NEW.pkey IS null THEN
               SET NEW.pkey = uuid();
            END IF;
$$
@delimiter ;

The DbVis page basically states that it is a level of abstraction above SQL and needs the extended syntax so that it can delimit parts of a more complex script.