MySQL Trigger problem with the email address (cause contains the @ sign)

Question

I'm trying to set a trigger in a table (users) to "copy" the email address of a new row to another table (prova). Here is the trigger:

CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO prova (provaemail)
    VALUES (NEW.email);
END

I obtain an empty field in the 'prova' table.

If I try to "copy" another field it's all ok, the problem seems to be only with the email address (cause contains the @ sign).

Answer 1

I ran an experiment. I created the following sample data and used your stored procedure:

drop database vito;
create database vito;
use vito
create table users
(id int not null auto_increment,
email varchar(255) not null,
primary key (id));
create table prova
(id int not null auto_increment,
provaemail varchar(255) not null,
primary key (id));
DELIMITER $$
CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW 
BEGIN 
    INSERT INTO prova (provaemail) 
    VALUES (NEW.email); 
END $$
DELIMITER ;
insert into users (email) values
('redwards@logicworks.net'),
('rolandoedwards@yahoo.com');
SELECT * FROM users;
SELECT * FROM prova;

Here is the result:

mysql> drop database vito;
Query OK, 2 rows affected (0.07 sec)

mysql> create database vito;
Query OK, 1 row affected (0.00 sec)

mysql> use vito
Database changed
mysql> create table users
    -> (id int not null auto_increment,
    -> email varchar(255) not null,
    -> primary key (id));
Query OK, 0 rows affected (0.11 sec)

mysql> create table prova
    -> (id int not null auto_increment,
    -> provaemail varchar(255) not null,
    -> primary key (id));
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW
    -> BEGIN
    ->     INSERT INTO prova (provaemail)
    ->     VALUES (NEW.email);
    -> END $$
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;
mysql> insert into users (email) values
    -> ('redwards@logicworks.net'),
    -> ('rolandoedwards@yahoo.com');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM users;
+----+--------------------------+
| id | email                    |
+----+--------------------------+
|  1 | redwards@logicworks.net  |
|  2 | rolandoedwards@yahoo.com |
+----+--------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM prova;
+----+--------------------------+
| id | provaemail               |
+----+--------------------------+
|  1 | redwards@logicworks.net  |
|  2 | rolandoedwards@yahoo.com |
+----+--------------------------+
2 rows in set (0.00 sec)

mysql>

Looks like the trigger works fine to me.

Rhetorical questions:

• Is the version of MySQL you are using source compiled or RPM/YUM installed ???
• Should you upgrade to the latest version of MySQL ???