Question 63734 in PhpMyAdmin Question posted in
The official documentation can be found here.

MySQL + phpMyAdmin, syntax error on trigger with IF statement

I am working on a small project, currently setting up a MySQL database. Unfortunately, this one piece of code is driving me crazy:

CREATE TRIGGER main_db.tg_make_competitor
AFTER UPDATE ON main_db.persons
FOR EACH ROW 
IF (NEW.permissions LIKE '%Competitor%') && (SELECT COUNT(*) FROM main_db.competitors WHERE competitor_id LIKE NEW.person_id) = 0 THEN
    INSERT INTO main_db.competitors(competitor_id)
    VALUES (NEW.person_id);
END IF;

My idea is that when an update on table persons occurs, the trigger should check whether a record has the permission of a Competitor (SET datatype) and if the competitor of Id = NEW.person_id does not exist – create one.

For me, everything seems fine. But phpMyAdmin throws an error:

#1064 - Something is wrong in your syntax near '' in line 6

Line six is this part:

VALUES (NEW.person_id);

Am I missing something? I’d appreciate any hints. Thanks in advance!

EDIT: Persons and Competitors Tables Structure:

CREATE TABLE main_db.persons 
(
    person_id INT,
    first_name VARCHAR(32) DEFAULT NULL,
    last_name VARCHAR(32) DEFAULT NULL,
    permissions SET('Standard', 'Competitor', 'Referee', 'Organizer', 'Director') DEFAULT 'Standard',
    FOREIGN KEY (person_id) REFERENCES main_db.accounts(account_id) ON DELETE CASCADE,
    PRIMARY KEY (person_id)
);

CREATE TABLE main_db.competitors 
(
    competitor_id INT,
    club VARCHAR(256) DEFAULT NULL,
    license VARCHAR(32) DEFAULT NULL,
    FOREIGN KEY (competitor_id) REFERENCES main_db.persons(person_id) ON DELETE CASCADE,
    PRIMARY KEY (competitor_id)
);

EDIT 2: Unfortunately code with added BEGIN … END does not work too :/

CREATE TRIGGER tg_make_competitor 
AFTER UPDATE ON persons
FOR EACH ROW
BEGIN
IF (NEW.permissions LIKE '%Competitor%') && (SELECT COUNT(*) FROM competitors WHERE competitor_id LIKE NEW.person_id) = 0 THEN
    INSERT INTO competitors(competitor_id)
    VALUES (NEW.person_id);
END IF;
END

SOLUTION

Thank’s for the Ergest Basha’s and Luuk’s answers it turned out that not using custom delimiters was the problem. After adding them – everything works like a charm.

Tags:

3

Answers


  1. 0

    There are no sql syntax error in your code. I have run this in db-fiddle and it’s working:

     create table persons (person_id int,permissions varchar(200));
 create table competitors (competitor_id int);

 CREATE TRIGGER tg_make_competitor
 AFTER UPDATE ON persons
 FOR EACH ROW 
 IF NEW.permissions = '%Competitor%' && (SELECT COUNT(*) FROM competitors WHERE competitor_id LIKE NEW.person_id) = 0 THEN
     INSERT INTO competitors(competitor_id)
     VALUES (NEW.person_id);
 END IF;

    db<>fiddle here

    Please check the value of persion_id

    Login or Signup to reply.
  2. 0

    You are missing BEGIN and END https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

    Tested on MySQL 8.0

    mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE persons
    -> (
    ->     person_id INT,
    ->     first_name VARCHAR(32) DEFAULT NULL,
    ->     last_name VARCHAR(32) DEFAULT NULL,
    ->     permissions SET('Standard', 'Competitor', 'Referee', 'Organizer', 'Director') DEFAULT 'Standard',
    ->     PRIMARY KEY (person_id)
    -> );
Query OK, 0 rows affected (0.78 sec)

mysql>
mysql>
mysql>
mysql> CREATE TABLE competitors
    -> (
    ->     competitor_id INT,
    ->     club VARCHAR(256) DEFAULT NULL,
    ->     license VARCHAR(32) DEFAULT NULL,
    ->     FOREIGN KEY (competitor_id) REFERENCES persons(person_id) ON DELETE CASCADE,
    ->     PRIMARY KEY (competitor_id)
    -> );
Query OK, 0 rows affected (0.52 sec)



mysql> DELIMITER //
mysql>  CREATE TRIGGER tg_make_competitor AFTER UPDATE ON persons
    ->     FOR EACH ROW
    ->     BEGIN
    ->          IF (NEW.permissions LIKE '%Competitor%') && (SELECT COUNT(*) FROM competitors WHERE competitor_id LIKE NEW.person_id) = 0 THEN
    ->                INSERT INTO competitors(competitor_id)
    ->                VALUES (NEW.person_id);
    ->          END IF;
    ->      END//
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql>  DELIMITER ;
    Login or Signup to reply.
  3. 0

    This code works in PhpMyAdmin:

    DELIMITER //
CREATE TRIGGER test.tg_make_competitor
AFTER UPDATE ON test.persons
FOR EACH ROW 
IF (NEW.permissions LIKE '%Competitor%') && (SELECT COUNT(*) FROM test.competitors WHERE competitor_id LIKE NEW.person_id) = 0 THEN
    INSERT INTO test.competitors(competitor_id)
    VALUES (NEW.person_id);
END IF;
//
DELIMITER ;

    It only produces this warning:

    Warning: #1287 '&&' is deprecated and will be removed in a future release. Please use AND instead

    Note: I did change main_db to test, when testing this locally. You might need to revert that change.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search