CODEWITHSUNDEEP
mysqlstored-procedurestriggers
Diep Thanh Tu
Diep Thanh Tu

Reputation: 43

Is there any problem in my trigger statement?

After i create my trigger statement, i cannot insert data normally but the program throws an error: error code 1109: Unknown table 'user' in field list. 

--create table
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL,
  `email` varchar(45) DEFAULT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `phone_number` int(11) DEFAULT NULL,
  `type` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--create trigger
DELIMITER $$
CREATE TRIGGER Checkinfo
    before INSERT ON dbi_202x.user FOR EACH ROW 
    BEGIN
        if( user.email not REGEXP '^[^@]+@[^@]+\.[^@]{2,}$')
        THEN 
            SET new.email = `wrong email`;
        end if;
    END;
$$
--insert data
INSERT INTO `dbi_202x`.`user`(`user_id`,`email`,`username`,`password`,`phone_number`,`type`)VALUES(
396812,
'[email protected]',
'sadfk23',
'asdf',
1369846978,
'employee');

Can you help me find out why this doesn't work?

Upvotes: 1

Views: 34

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562508

You can test a regexp in the MySQL client without using a trigger:

mysql> select '[email protected]' REGEXP '^[^@]+@[^@]+\.[^@]{2,}$' as email_match;
+-------------+
| email_match |
+-------------+
|           1 |
+-------------+

That's expected, but how about if we give it an invalid email?

mysql> select 'user@example.' REGEXP '^[^@]+@[^@]+\.[^@]{2,}$' as email_match;
+-------------+
| email_match |
+-------------+
|           1 |
+-------------+

That should be a 0, but why isn't it?

Because \. isn't enough to escape the dot in the regular expression. A single \ gets lost as SQL is parsing the string. We need a literal \ to be in the regular expression string, to escape the dot. So we have to use a double \\ so that a single literal \ remains in the regular expression after MySQL has scanned it once.

mysql> select 'user@example.' REGEXP '^[^@]+@[^@]+\\.[^@]{2,}$' as email_match;
+-------------+
| email_match |
+-------------+
|           0 |
+-------------+

P.S. For what it's worth, emails are more complex than your simple validation. This old archived blog has several attempts at a comprehensive email validation: https://web.archive.org/web/20150910045413/http://squiloople.com/2009/12/20/email-address-validation

The most successful of these became the regular expression used in PHP's FILTER_VALIDATE_EMAIL.

Upvotes: 1

Related Questions