i hope someone can help with this super silly question. I have a web page nogunsdrugsbets.com where I have set a simple comment section where anybody can leave a comment but instead of people commenting (i think i have not many visitors :(), what I have is hundreds of long messages from bots sending me to gay pages, porno or similar. My intention is automate an after insert trigger which deletes any row that has the word 'http' in it, in other words, i kill all the bots' messages because they put url's sending you to other pages.

I don't know much about sql but the below code works when I write in SQL section in each table (affecting all the rows that contain 'http' in the message) but it doesn´t work if I put as a Trigger, instead it blocks all the messages, including the one that don't contain http.... The problem is that I need a trigger because i can't connect constantly to each table to do that everytime, because the bots are writing hundreds of messages all the time. One week i didn't look the page and I found 10000 messages that were blocking the page.

Anyways, the code is simple:

DELETE FROM commentsmain WHERE message LIKE '%http%'

This is the AFTER INSERT Trigger i try to set up

Here is the complete code of the trigger:

DROP TRIGGER IF EXISTS spamMain;CREATE DEFINER=u956484391_database@127.0.0.1 TRIGGER spamMain AFTER INSERT ON commentsmain FOR EACH ROW DELETE FROM commentsmain WHERE message LIKE '%http%'

Thanks so much.

I have looked all around but I can't find anything that relates to this problem I am experiencing. Thanks so much.

CodePudding user response：

You can't delete the row you're in the act of inserting as part of an AFTER INSERT trigger.

CodePudding user response：

I think Check Constraint is what you need

If you define a CHECK constraint on a column it will allow only certain values for this column.