MySQL phpMyAdmin, syntax error on trigger with IF statement-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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 ;

CodePudding user response：

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