MySQL IF statement in a trigger- ELSE don't do anything

Question

I am trying to build a trigger in MySQL that will run after new rows are inserted into a table. If there is an error detected, then I want to write that trigger to a table. Otherwise, I don't want to write the error to a table. I want to try and avoid adding endless blank rows.

Here is my code:

CREATE 
TRIGGER character_validation4 AFTER INSERT 
ON address FOR EACH ROW 
Begin 
IF(New.city REGEXP '^[^a-zA-Z0-9]') 
THEN 
INSERT INTO errorlog (city, mainprocessid) Values("There is an error with the city", New.ID));
END

But I am seeing an error on the final bracket.

Can anyone help? Thanks

Kai · Accepted Answer · 2013-04-18 10:34:38Z

2

DELIMITER \\

CREATE 
TRIGGER character_validation4 AFTER INSERT 
ON address FOR EACH ROW 
Begin 
IF(New.city REGEXP '^[^a-zA-Z0-9]') 
THEN 
INSERT INTO errorlog (city, mainprocessid) Values("There is an error with the city", New.ID);
END IF;
END \\

DELIMITER ;

edited Apr 18, 2013 at 10:34

answered Apr 18, 2013 at 9:33

Kai

2,1888 gold badges29 silver badges50 bronze badges

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

2 Comments

Iain Fifer Over a year ago

Thanks, but now I see "Unexpected End_of_Input expecting ';'" and "unexpected end" and "unexpected end"

Kai Over a year ago

I noticed the phpmyadmin tag, so I assume you're typing this into there. Add DELIMITER // to the beginning on the query (before create) and add DELIMITER ; at the end. Edit: also add // after the last END. See edits to my answer.

SoftwareCarpenter · Accepted Answer · 2013-04-18 10:26:23Z

1

It appears that you have an extra ")" right after New.ID . Try it this way

CREATE TRIGGER character_validation4 
AFTER INSERT ON address FOR EACH ROW Begin 
IF(New.city REGEXP '^[^a-zA-Z0-9]') THEN INSERT INTO errorlog (city, mainprocessid) 
Values("There is an error with the city", New.ID);
END IF;
END;

edited Apr 18, 2013 at 10:26

answered Apr 18, 2013 at 9:33

SoftwareCarpenter

3,9233 gold badges29 silver badges38 bronze badges

2 Comments

Iain Fifer Over a year ago

Thanks, but now I see "Unexpected End_of_Input expecting ';'" and "unexpected end"

SoftwareCarpenter Over a year ago

Add a END IF and put a ; after the end statement

Collectives™ on Stack Overflow

MySQL IF statement in a trigger- ELSE don't do anything

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related