MySQL Trigger Syntax Error #1064

Question

I am trying to implement a trigger in MySQL that will automatically add/modify a column when another one is added/modified. Basically I want it to save a type as a slug in the type_sanitized column when a value is added/modified into type.

This is my initial try:

DROP TRIGGER IF EXISTS ArticleTypes.insert_sanitized_article_type;

CREATE TRIGGER 'insert_sanitized_article_type'
AFTER INSERT
ON 'ArticleTypes'
FOR EACH ROW
SET NEW.type_sanitized = LOWER(REPLACE(TRIM(NEW.type), ' ', '-'));

DROP TRIGGER IF EXISTS ArticleTypes.update_sanitized_article_type;

CREATE TRIGGER 'update_sanitized_article_type'
AFTER UPDATE
ON 'ArticleTypes'
FOR EACH ROW 
SET NEW.type_sanitized = LOWER(REPLACE(TRIM(NEW.type), ' ', '-'));

After seeing suggestions online, I have tried adding delimiters like this:

DROP TRIGGER IF EXISTS ArticleTypes.insert_sanitized_article_type;

DELIMITER $$
CREATE TRIGGER 'insert_sanitized_article_type'
AFTER INSERT
ON 'ArticleTypes'
FOR EACH ROW 
BEGIN
SET NEW.type_sanitized = LOWER(REPLACE(TRIM(NEW.type), ' ', '-'));
END$$
DELIMITER ;

DROP TRIGGER IF EXISTS ArticleTypes.update_sanitized_article_type;

DELIMITER $$
CREATE TRIGGER 'update_sanitized_article_type'
AFTER UPDATE
ON 'ArticleTypes'
FOR EACH ROW 
BEGIN
SET NEW.type_sanitized = LOWER(REPLACE(TRIM(NEW.type), ' ', '-'));
END$$
DELIMITER ;

I get the following error for both of them:

#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 ''insert_sanitized_article_title'
AFTER INSERT
ON 'ArticleTypes'
FOR EACH ROW 
BE' at line 1

This is my first time creating triggers in MySQL so it might be obvious but I haven't been able to figure it out in 2 hours of searching so any help would be greatly appreciated.

Vlad Schnakovszki · Accepted Answer · 2014-03-14 15:59:38Z

1

Remove the apostrophes.

DROP TRIGGER IF EXISTS ArticleTypes.insert_sanitized_article_type;

DELIMITER $$
CREATE TRIGGER insert_sanitized_article_type
AFTER INSERT
ON ArticleTypes
FOR EACH ROW 
BEGIN
SET @type_sanitized = LOWER(REPLACE(TRIM(@type), ' ', '-'));
END$$
DELIMITER ;

DROP TRIGGER IF EXISTS ArticleTypes.update_sanitized_article_type;

DELIMITER $$
CREATE TRIGGER update_sanitized_article_type
AFTER UPDATE
ON ArticleTypes
FOR EACH ROW 
BEGIN
SET @type_sanitized = LOWER(REPLACE(TRIM(@type), ' ', '-'));
END$$
DELIMITER ;

edited Mar 14, 2014 at 15:59

Vlad Schnakovszki

8,6126 gold badges84 silver badges115 bronze badges

answered Mar 14, 2014 at 15:53

Alexander

3,1992 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vlad Schnakovszki Over a year ago

Ahhhh such a minor detail ... That fixed it, thank you!

Vlad Schnakovszki Over a year ago

However, nothing is added to type_sanitized when inserting or updating type. Any ideas why?

Vlad Schnakovszki Over a year ago

ArticleTypes only includes type and type_sanitized, which are both VARCHAR(100). type is PK, type_sanitized is unique.

Collectives™ on Stack Overflow

MySQL Trigger Syntax Error #1064

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related