Block .Text Blog Spam

I am very tired of the comments of the style “Texas Online Casino” and as the version of .Text that I use doesn’t implement any mechanism to avoid this type of comments I modify this trigger a little to avoid this situacion.

CREATE TRIGGER blog_Content_Trigger
ON blog_Content AFTER INSERT, UPDATE, Delete

AS

DECLARE @BlogID INT
DECLARE @ID INT
DECLARE @PostType INT
DECLARE @ParentID INT

SELECT @BlogID = BlogID, @ID = ID FROM INSERTED

IF (@BlogID IS NULL)
BEGIN
   SELECT @BlogID = BlogID, @ID = ID  FROM DELETED 
END

SELECT @ParentID = ParentID, @PostType = PostType
FROM blog_Content
WHERE [ID] = @ID
–Add more “spam words” here
AND (   [Text] LIKE ‘%poker%’
     OR [Text] LIKE ‘%pharmacy%’
     OR [Text] LIKE ‘%pharmacies%’
     OR [Text] LIKE ‘%diet pill%’
     OR [Text] LIKE ‘%canadian drugs%’
     OR [Text] LIKE ‘%wellbutrin%’
     OR [Text] LIKE ‘%valium%’
     OR [Text] LIKE ‘%prozac%’
     OR [Text] LIKE ‘%wrinkle cream%’
     OR [Text] LIKE ‘%proactive skin care%’
     OR [Text] LIKE ‘%quick weight loss%’
     OR [Text] LIKE ‘%viagra%’
     OR [Text] LIKE ‘%provigil%’
     OR [Text] LIKE ‘%ativan%’
     OR [Text] LIKE ‘%protonix%’
     OR [Text] LIKE ‘%levitra%’
     OR [Text] LIKE ‘%meridia%’
     OR [Text] LIKE ‘%casino%’
     OR [Text] LIKE ‘%loan%’
     OR [Text] LIKE ‘%loans%’
     OR [Text] LIKE ‘%gambling%’
     OR [Text] LIKE ‘%free slots%’
     OR [Text] LIKE ‘%roulette%’
     OR [Text] LIKE ‘%slot machines%’
     OR [Text] LIKE ‘%neurontin%’
     OR [Text] LIKE ‘%allegra%’
     OR [Text] LIKE ‘%drugs%’
     OR [Text] LIKE ‘%phentermine%’
     OR [Text] LIKE ‘%prescriptions%’
     OR [Text] LIKE ‘%medications%’
     OR [Text] LIKE ‘%texas%’
     OR [Text] LIKE ‘%mortgage%’
     OR [Text] LIKE ‘%4best-health%’
     OR [Text] LIKE ‘%amoxicillin%’
     OR [Text] LIKE ‘%blackjack%’ 
     OR [Text] LIKE ‘%back jack%’)

IF (@PostType IS NOT NULL)
BEGIN
   DELETE FROM blog_Content
   WHERE [ID] = @ID

   IF (@PostType = 3 or @PostType = 4)
   BEGIN
      UPDATE blog_Content
      SET FeedBackCount = FeedBackCount – 1
      WHERE [ID] = @ParentID
   END
END

UPDATE blog_Config
SET
PostCount = (SELECT COUNT(*) FROM blog_Content WHERE blog_Content.BlogID = blog_Config.BlogID AND PostType = 1 AND PostConfig & 1 = 1),
CommentCount = (SELECT COUNT(*) FROM blog_Content WHERE blog_Content.BlogID = blog_Config.BlogID AND PostType = 3 AND PostConfig & 1 = 1),
StoryCount = (SELECT COUNT(*) FROM blog_Content WHERE blog_Content.BlogID = blog_Config.BlogID AND PostType = 2 AND PostConfig & 1 = 1),
PingTrackCount = (SELECT COUNT(*) FROM blog_Content WHERE blog_Content.BlogID = blog_Config.BlogID AND PostType = 4 AND PostConfig & 1 = 1)
WHERE BlogID = @BlogID