MariaDB not allowing to use SIGNAL properly

Question

Likely related: MySQL trigger definition - 1064 error

Hi,

I'm trying to add a trigger on to my User-Table in a MariaDB 10.4.10-GA. The intention is, that a User can set a second Mail-Address, but that Mail-Address must not be the same as the first Mail-Address. The Code for the given Trigger:

CREATE TRIGGER MyValidator
  BEFORE INSERT
  ON User
  FOR EACH ROW
BEGIN
  IF NEW.U_Mail LIKE '%_@%_.%' THEN
    IF NEW.U_AlternateMail LIKE NEW.U_Mail THEN
      SIGNAL SQLSTATE VALUE '45001'
      SET MESSAGE_TEXT = 'User - Mail already set';
    ELSEIF NEW.U_AlternateMail NOT LIKE '%_@%_.%' THEN
      SIGNAL SQLSTATE VALUE '45002'
      SET MESSAGE_TEXT = 'User - Not a Mail';
    END IF;
  ELSE
    SIGNAL SQLSTATE VALUE '45000'
    SET MESSAGE_TEXT = 'User - Bad Mail in database';
  END IF;
END;

MariaDB tells me:

#1064 - 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 9

which would be the SET statement. And even just adding a SET testvar="text"; after BEGIN. Yields the same error, just with that new line as the erroring line. I have been sitting on that issue for serveral hours now and can't find anything useful on the internet. According to the Docs this SQL-Code should be correct: https://mariadb.com/kb/en/library/signal/ Interresetingsly, this example code from the aforementioned link also doesn't work with the same error:

CREATE PROCEDURE test_error(x INT)
BEGIN
   DECLARE errno SMALLINT UNSIGNED DEFAULT 31001;
   SET @errmsg = 'Hello, world!';
   IF x = 1 THEN
      SIGNAL SQLSTATE '45000' SET
      MYSQL_ERRNO = errno,
      MESSAGE_TEXT = @errmsg;
   ELSE
      SIGNAL SQLSTATE '45000' SET
      MYSQL_ERRNO = errno,
      MESSAGE_TEXT = _utf8'Hello, world!';
   END IF;
END;

Any ideas, what the issue might be?

Greetings

Answer 1

Ensure to use the delimiter when adding triggers, functions, or stored procedure definitions.

Not sure if you are using the command line or some other tool, such as workbench, or phpmyadmin, but it would be the same either way.

I pasted your trigger statement into my command line with the delimiter:

delimiter //
CREATE TRIGGER MyValidator
  BEFORE INSERT
  ON User
  FOR EACH ROW
BEGIN
  IF NEW.U_Mail LIKE '%_@%_.%' THEN
    IF NEW.U_AlternateMail LIKE NEW.U_Mail THEN
      SIGNAL SQLSTATE VALUE '45001'
      SET MESSAGE_TEXT = 'User - Mail already set';
    ELSEIF NEW.U_AlternateMail NOT LIKE '%_@%_.%' THEN
      SIGNAL SQLSTATE VALUE '45002'
      SET MESSAGE_TEXT = 'User - Not a Mail';
    END IF;
  ELSE
    SIGNAL SQLSTATE VALUE '45000'
    SET MESSAGE_TEXT = 'User - Bad Mail in database';
  END IF;
END;
//

When run (after first changing to the database), the output was:

MariaDB [(none)]> use test
Database changed
MariaDB [test]> delimiter //
MariaDB [test]> CREATE TRIGGER MyValidator
    ->   BEFORE INSERT
    ->   ON User
    ->   FOR EACH ROW
    -> BEGIN
    ->   IF NEW.U_Mail LIKE '%_@%_.%' THEN
    ->     IF NEW.U_AlternateMail LIKE NEW.U_Mail THEN
    ->       SIGNAL SQLSTATE VALUE '45001'
    ->       SET MESSAGE_TEXT = 'User - Mail already set';
    ->     ELSEIF NEW.U_AlternateMail NOT LIKE '%_@%_.%' THEN
    ->       SIGNAL SQLSTATE VALUE '45002'
    ->       SET MESSAGE_TEXT = 'User - Not a Mail';
    ->     END IF;
    ->   ELSE
    ->     SIGNAL SQLSTATE VALUE '45000'
    ->     SET MESSAGE_TEXT = 'User - Bad Mail in database';
    ->   END IF;
    -> END;
    -> //
Query OK, 0 rows affected (0.11 sec)

...and then checking the information_schema afterwards:

MariaDB [test]> select trigger_schema, event_object_table, action_timing, action_statement from information_schema.triggers where trigger_name = 'MyValidator' \G
*************************** 1. row ***************************
    trigger_schema: test
event_object_table: user
     action_timing: BEFORE
  action_statement: BEGIN
  IF NEW.U_Mail LIKE '%_@%_.%' THEN
    IF NEW.U_AlternateMail LIKE NEW.U_Mail THEN
      SIGNAL SQLSTATE VALUE '45001'
      SET MESSAGE_TEXT = 'User - Mail already set';
    ELSEIF NEW.U_AlternateMail NOT LIKE '%_@%_.%' THEN
      SIGNAL SQLSTATE VALUE '45002'
      SET MESSAGE_TEXT = 'User - Not a Mail';
    END IF;
  ELSE
    SIGNAL SQLSTATE VALUE '45000'
    SET MESSAGE_TEXT = 'User - Bad Mail in database';
  END IF;
END
1 row in set (0.01 sec)