Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 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 ???
share|improve this answer
    
Thank's, I tryed also. It's all fine. The problem it's only when a new row it's created by SQLAlchemy(+Plone) ... argh! –  Vito Jan 12 '12 at 8:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.