Creating trigger for table in MySQL database (syntax error)

I have trouble defining a trigger for a MySQL database. I want to change a textfield before inserting a new row (under a given condition). This is what I have tried:

CREATE TRIGGER add_bcc
BEFORE INSERT ON MailQueue
FOR EACH ROW BEGIN
  IF (NEW.sHeaders LIKE "%<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a5d6d0d5d5cad7d1e5c8dcc1cac8c4cccb8bc6cac8">[email protected]</a>%") THEN
    SET NEW.sHeaders = NEW.sHeaders + "BCC:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="1f76716b7a6d717e735f72667b70727e7671317c7072">[email protected]</a>";
  END IF;
END;

But always I get the error “wrong syntax”. I got stuck, what am I doing wrong?
I’m using MySQL 5.0.51a-community

BTW: Creating an empty Trigger like this works fine:

CREATE TRIGGER add_bcc
BEFORE INSERT ON MailQueue
FOR EACH ROW BEGIN
END;

But this fails, too:

CREATE TRIGGER add_bcc
BEFORE INSERT ON MailQueue 
FOR EACH ROW BEGIN
  IF 1=1 THEN
  END IF; 
END;

It’s my first time to use stackoverflow.com, so I’m very excited if it is helpful to post something here 🙂

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You need to change the delimiter – MySQL is seeing the first “;” as the end of the CREATE TRIGGER statement.

Try this:

/* Change the delimiter so we can use ";" within the CREATE TRIGGER */
DELIMITER $$

CREATE TRIGGER add_bcc
BEFORE INSERT ON MailQueue
FOR EACH ROW BEGIN
  IF (NEW.sHeaders LIKE "%<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a9dadcd9d9c6dbdde9c4d0cdc6c4c8c0c787cac6c4">[email protected]</a>%") THEN
    SET NEW.sHeaders = NEW.sHeaders + "BCC:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="f39a9d8796819d929fb39e8a979c9e929a9ddd909c9e">[email protected]</a>";
  END IF;
END$$
/* This is now "END$$" not "END;" */

/* Reset the delimiter back to ";" */
DELIMITER ;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating