Creating trigger for table in MySQL database (syntax error)

Question

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 "%[email protected]%") THEN
    SET NEW.sHeaders = NEW.sHeaders + "BCC:[email protected]";
  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 :-)

Answer 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 "%[email protected]%") THEN
    SET NEW.sHeaders = NEW.sHeaders + "BCC:[email protected]";
  END IF;
END$$
/* This is now "END$$" not "END;" */

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