CODEWITHSUNDEEP
mysqltriggersmariadb
Pushpendra
Pushpendra

Reputation: 4382

Trigger code issues with MySQL

I have a trigger in the MariaDb Server with following code: 

    DELIMITER //
    CREATE TRIGGER `set_app_number` 
    BEFORE INSERT ON `table_name` FOR EACH ROW BEGIN
        DECLARE newAppNo INTEGER;
        SELECT Max(id)+1 INTO newAppNo FROM table_name;
        SET NEW.application_number = newAppNo; 
    END; //
    DELIMITER ;

I am trying to create the same trigger on the production server where I have MySQL(5.7+ version). Where I am getting error like:

Error Static analysis:

6 errors were found during analysis.

  • Unexpected beginning of statement. (near "CREATE TRIGGER " at position 0)
  • Unexpected beginning of statement. (near "set_app_number" at position 15)
  • Unexpected beginning of statement. (near " BEFORE INSERT ON " at position 39)
  • Unexpected beginning of statement. (near "table_name" at position 57)
  • Unexpected beginning of statement. (near " FOR EACH ROW BEGIN" at position 74)
  • Unexpected beginning of statement. (near "DECLARE newAppNo INTEGER" at position 95)

SQL query:

CREATE TRIGGER set_app_number BEFORE INSERT ON table_name FOR EACH ROW BEGIN DECLARE newAppNo INTEGER

MySQL said: Documentation

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

near 'CREATE TRIGGER set_app_number BEFORE INSERT ON table_name' at line 1

I am not sure what's the exact issue here. Thanks in advance.

Update

When trying to add a trigger from phpMyAdmin using More -> Trigger -> Add Trigger, the very same code is generated. I am not sure why it is not allowing the same when executing it from SQL. :(

Upvotes: 0

Views: 592

Answers (1)

sajushko
sajushko

Reputation: 420

Remove delimiters a run it as query

CREATE TRIGGER `set_app_number` 
    BEFORE INSERT ON 
        `table_name` 
    FOR EACH ROW 
    BEGIN
        DECLARE newAppNo INTEGER;
        SELECT Max(id)+1 INTO newAppNo FROM table_name;
        SET NEW.application_number = newAppNo; 
    END;

Upvotes: 0

Related Questions