1

We're currently looking to port from Oracle into MariaDb, but are struggling to recreate old triggers.

Specifically, we're currently trying to execute:

CREATE DEFINER=`admin`@`%` TRIGGER SET_AUTHOR_EVENT_ID BEFORE INSERT ON author_event
FOR EACH ROW 
BEGIN
    SET NEW.ID = AUTHOR_EVENT_ID_SEQ.NEXTVAL;
END;

but are getting the following error message :

SQL Error [1064] [42000]: (conn=3153) 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 1

The syntax we're using appears to be inline with the documentation we can find, but there's definitely an error in there somewhere. Looking into the specifics of the error message (i.e. 1064) appears to yield few clues...any help/pointers/suggestions gratefully received.

Thanks

Paul T.
  • 4,703
  • 11
  • 25
  • 29
Phil S
  • 123
  • 8
  • Did you use a `delimiter`? ... see the example usage with a trigger [here](https://mariadb.com/kb/en/trigger-overview/#more-complex-triggers) ... and the [sequence](https://mariadb.com/kb/en/sequences/) may be an issue depending on the version, introduced with v10.3. – Paul T. Nov 18 '20 at 01:57
  • ...(more) I was able to create the trigger you have using a delimiter (but with a different definer value), but then got an error on the sequence for me. My version is 10.1.21 – Paul T. Nov 18 '20 at 02:41

1 Answers1

0

Adding DELIMITER as Paul said, worked for me.

DELIMITER $$    
CREATE DEFINER=`admin`@`%` TRIGGER SET_AUTHOR_EVENT_ID BEFORE INSERT ON author_event
FOR EACH ROW 
BEGIN
    SET NEW.ID = AUTHOR_EVENT_ID_SEQ.NEXTVAL;
END; 
$$
andreyro
  • 935
  • 12
  • 18