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 ;

Answer 2

A trigger can run multiple SQL statements automatically before or after a table is changed with INSERT, UPDATE or DELETE. *The doc explains a trigger in detail.

For example, you create test table as shown below:

CREATE TABLE test (
  num int
);

Then, you insert the row whose num is 2 as shown below:

INSERT INTO test (num) VALUES (2);

Next, you create log table as shown below:

CREATE TABLE log (
  ins INT,
  upd INT,
  del INT
);

Then, you insert the row whose ins, upd and del are 0 as shown below:

INSERT INTO log (ins, upd, del) VALUES (0, 0, 0);

Now, you can create count trigger which adds 1 to upd in log table after a test table's row is updated as shown below. *You can replace AFTER and UPDATE with BEFORE and INSERT or DELETE respectively to run count trigger before a row is inserted to test table or a test table's row is deleted and in a trigger, SELECT INTO is allowed to use but SELECT without INTO is not allowed to use because there is the error and basically, you need to change the default delimiter ; to something like $$ when creating a trigger otherwise there is error, then after creating a trigger, you need to change the delimiter $$ back to ; as shown below and my answer explains delimiter and you must select a database when creating a procedure otherwise there is the error:

DELIMITER $$

CREATE TRIGGER count
AFTER UPDATE ON test FOR EACH ROW
BEGIN
UPDATE log SET upd = upd + 1;
END$$

DELIMITER ;

Then, you update num from 2 to 4 in test table, then upd is 1 as shown below:

mysql> UPDATE test SET num = 4;
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|    4 |
+------+
...
mysql> SELECT * FROM log;       
+------+------+------+
| ins  | upd  | del  |
+------+------+------+
|    0 |    1 |    0 |
+------+------+------+

In addition, you can get the num values which are before and after update with OLD.num and NEW.num respectively. *The num values are stored in the user-defined session variable @old_num and @new_num respectively and you cannot use OLD.num and NEW.num for INSERT and DELETE respectively because there are the error and the error respectively:

DELIMITER $$

CREATE TRIGGER count
AFTER UPDATE ON test FOR EACH ROW
BEGIN
UPDATE log SET upd = upd + 1;
SET @old_num = OLD.num, @new_num = NEW.num;
END$$        -- ↑ Here           -- ↑ Here

DELIMITER ;

Then, @old_num and @new_num are 2 and 4 respectively after updating num from 2 to 4 in test table as shown below:

mysql> UPDATE test SET num = 4;
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|    4 |
+------+
...
mysql> SELECT * FROM log;       
+------+------+------+
| ins  | upd  | del  |
+------+------+------+
|    0 |    1 |    0 |
+------+------+------+
...
mysql> SELECT @old_num, @new_num;
+----------+----------+
| @old_num | @new_num |
+----------+----------+
|        2 |        4 |
+----------+----------+

And, in this case below, you can create count trigger not changing the delimiter to $$ without error:

CREATE TRIGGER count
AFTER UPDATE ON test FOR EACH ROW
UPDATE log SET upd = upd + 1;