MySQL modify row value on before insert trigger-CodePudding

I am working on a Netflix like database. I would like to create a trigger which is called whenever a new content in added to the database. What it should do is, if the content's name is like "The Return of the King", replace it with "Return of the King, The".

Currently I have the following trigger:

DROP TRIGGER IF EXISTS `UpdateContentName`;
       CREATE DEFINER=`root`@`localhost` TRIGGER `UpdateContentName` 
       BEFORE INSERT ON `content` FOR EACH ROW  
       BEGIN
            SET NEW.`name_content` = 'teeeeeest' 
            WHERE content.name_content LIKE = "The %";
        END

But I got the following error message:

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 'WHERE content.name_content LIKE = "The %"; END' at line 5

It looks like the issue comes from the WHERE content.name_content LIKE = "The %"; line since it works without it but I don't understand what I should do... I have spent several hours trying to solve this and didn't manage to find a solution...

To sum up I'd like to achieve something like:

If(name_content.contains("The %")) 
   //name_content in the form "name of the content, The" instead of "The name of the content"

Thanks for your help! :)

CodePudding user response：

You need to check the condition before you set it

DELIMITER $$
DROP TRIGGER IF EXISTS `UpdateContentName`;
CREATE DEFINER=`root`@`localhost` TRIGGER `UpdateContentName` 
BEFORE INSERT ON `content` FOR EACH ROW  
BEGIN
    IF  NEW.name_content LIKE "The %" THEN
        SET NEW.`name_content` = 'teeeeeest';
    END IF;     
END$$
DELIMITER ;

CREATE tABLE content( name_content varchar(100))

CREATE  TRIGGER `UpdateContentName` 
BEFORE INSERT ON `content` FOR EACH ROW  
BEGIN
    IF  NEW.name_content LIKE "The %" THEN
        SET NEW.`name_content` = 'teeeeeest';
    END IF;     
END

INSERT INTO content VALUES('The AbC')

SELECT * FROM content

| name_content |
| :----------- |
| teeeeeest    |

db<>fiddle here