vendredi 1 janvier 2021

Removing "The " with an IF statement in MYSQL Before Insert Trigger

Hello helpful world of Stack Overflow! I am brand new to Triggers, but am having fun using them in my new project. I have already gotten a few done, but am struggling with setting up a sort field by removing "The " from the beginning of a name.

Being new to this, I am using PHP MyAdmin to build my triggers. So I have it set to my 'aartist' table, Before Insert. The 2 fields are artist_name & artist_sort. This is the full code I've tried with no luck.

BEGIN
 IF (left(new.artist_name,4) != "The ")
   THEN
    set new.artist_sort = new.artist_name
   ELSE
    set new.artist_sort = right(new.artist_name,len(new.artist_name)-4)
   END IF;
END

I've also tried stripping it back for something more simple, still without luck. The IF statement seems to not work in the TRIGGER.

BEGIN
 IF (new.artist_name != new.artist_sort) THEN
    set new.artist_sort = new.artist_name
   END IF;
   
END

The Error PHPMyAdmin gives is:

 One or more errors have occurred while processing your request:
The following query has failed: "CREATE DEFINER=`####`@`localhost` TRIGGER `AddArtist` BEFORE INSERT ON `aartist` FOR EACH ROW BEGIN IF (new.artist_name != new.artist_sort) THEN set new.artist_sort = new.artist_name END IF; END"
MySQL said: #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 'END IF; END' at line 4

Thank you, as always, for any help in figure out what I might be doing wrong with this.

Aucun commentaire:

Enregistrer un commentaire