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:
x
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 🙁
Advertisement
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 ;