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 :-)

What happens if you remove "SET"? Also you should change NEW.sHeaders + "BCC:[email protected]" to CONCAT(NEW.sHeaders, "BCC:[email protected]") — Greg
– Greg, Commented Jan 22, 2009 at 16:24
I'm making this a comment not an answer because it's a guess :p — Greg
– Greg, Commented Jan 22, 2009 at 16:24
Thanks! I've tried it, but the syntax error seems to be caused by the if statement. Even the following fails: CREATE TRIGGER add_bcc BEFORE INSERT ON MailQueue FOR EACH ROW BEGIN IF 1=1 THEN END IF; END; Sadly, MySQL does not give any good hints what the error is about... — Georg Ledermann
– Georg Ledermann, Commented Jan 22, 2009 at 16:28

Greg · Accepted Answer · 2009-01-22 16:38:27Z

29

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 ;

answered Jan 22, 2009 at 16:38

Greg

323k55 gold badges378 silver badges338 bronze badges

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

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2023-12-30 01:33:50Z

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;

Collectives™ on Stack Overflow

Creating trigger for table in MySQL database (syntax error)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related