How can I make my sql trigger about inappropiate words work

I have a database with different tables in order to store photos, users, reviews, ratings… I wanna validate that no one uses “bad words” (insults) in their photos’ title or description. So I decided to create a table called ‘inappropiatewords’ where all of these bad words will be stored and then I made the following trigger:

DELIMITER //
CREATE OR REPLACE TRIGGER tBadWords_RNC02
    BEFORE INSERT ON Photos
    FOR EACH ROW
    BEGIN
        DECLARE numwords INT;
        SET numwords = (SELECT COUNT(*) FROM inappropiatewords);
        DECLARE title VARCHAR(128);
        DECLARE description VARCHAR(512);
        SET title = (SELECT title FROM Photos WHERE photoId = new.photoId);
        SET description = (SELECT description FROM Photos WHERE photoId = new.photoId);
        DECLARE x INT;
        SET x = 1;
        WHILE x <= numwords DO
            DECLARE word VARCHAR(200);
            SET word = (SELECT word FROM inappropiatewords WHERE inappropiateWordId = x);
            IF(INSTR(title,word) > 0 OR INSTR(description,word) > 0) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot use inappropiate words in title or description';
            END IF;
    END //
DELIMITER ;

But when I try to run it I’m getting the following error: [error image][1]

I don’t know what I’m doing bad, so if anybody could help me I would be so grateful. I’m using HeidiSQL and MariaDB. [1]: https://i.stack.imgur.com/FFc6a.jpg

UPDATE I’ve made the following change:

DELIMITER //
CREATE OR REPLACE TRIGGER tBadWords_RNC02
    BEFORE INSERT ON Photos
    FOR EACH ROW
    BEGIN
        DECLARE numwords INT;
        SET numwords = (SELECT COUNT(*) FROM inappropiatewords);
        FOR x IN 1..numwords DO
            DECLARE word VARCHAR(200);
            SET word = (SELECT word FROM inappropiatewords WHERE inappropiateWordId = x);
            IF(INSTR(NEW.title,word) > 0 OR INSTR(NEW.description,word) > 0) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot use inappropiate words in title or description';
            END IF;
    END //
DELIMITER ;

but I’m still getting an error 🙁

Answer

It can be a bit simpler

DELIMITER //
CREATE OR REPLACE TRIGGER tBadWords_RNC02
    BEFORE INSERT ON Photos
    FOR EACH ROW
    BEGIN
       IF (EXISTS (
            SELECT 1 
            FROM inappropiatewords bw
            WHERE INSTR(NEW.title, bw.word) > 0 OR INSTR(NEW.description,  bw.word) > 0
          ))
       THEN
         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot use inappropiate words in title or description';
       END IF;
END //
DELIMITER ;

db<>fiddle

Advertisement

Answer