How can I properly use a trigger inside of stored procedure to validate an email?

Question

-- Create a trigger inside of stored procedure that will enforce that Faculty's Email has to be
-- used in a standard way. 

USE College ;


DROP PROCEDURE IF EXISTS Faculty_Before_Insert;

DELIMITER $$

CREATE PROCEDURE Faculty_Before_Insert()
BEGIN 


        CREATE TRIGGER Review_Email_Before
        BEFORE INSERT ON Faculty
        FOR EACH ROW 

      BEGIN 
            DECLARE Email varchar(255); 

        IF Email != '^\w+(\.\w+)*+@\w+(\.\w+)+$'  THEN 
          SIGNAL SQLSTATE  'HY000'
             SET MESSAGE_TEXT= 'This email doesn\'t match [email protected]'; 
        END IF ; 

      END;

END
$$

DELIMITER ;



INSERT INTO Faculty(LastName, FirstName, Email, HireDate, Salary, DepartmentID) 
              VALUES('Stanley', 'Mike', '[email protected]', current_date(), 20000, 1); 


SELECT 
Faculty.LastName, 
Faculty.FirstName, 
Faculty.Email 
 FROM  Faculty 
 WHERE ID= last_insert_id(); 

I am working in MySQL Workbench 6.3 CE and trying to use a trigger inside of a stored procedure to see if it will detect if my email format is not in the right format I specified in the message text error, but I am having trouble because it says a trigger can't be created inside of stored procedure. How else can I accomplish what I need to accomplish. This is a BEFORE TRIGGER since it will check the email validation before any rows are inserted into my Faculty table. What I am I doing wrong? Here is my SQL code below:

Answer 1

Triggers are created on actual tables, and are automatically executed when an action is performed on the table in question, they're not actually created within procedures.

So, given the table:

create table Faculty
(
LastName varchar(100), 
FirstName varchar(100), 
Email varchar(100), 
HireDate date, 
Salary integer,
DepartmentID integer
);

... your trigger code, once removed from the procedure, does work:

mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER Review_Email_Before
    ->         BEFORE INSERT ON Faculty
    ->         FOR EACH ROW
    ->       BEGIN
    ->         IF new.Email != '^\w+(\.\w+)*+@\w+(\.\w+)+$'
    ->         THEN
    ->           SIGNAL SQLSTATE  'HY000'
    ->              SET MESSAGE_TEXT= 'This email doesn\'t match [email protected]';
    ->         END IF ;
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> INSERT INTO Faculty(LastName, FirstName, Email, HireDate, Salary, DepartmentID)
    ->               VALUES('Stanley', 'Mike', '[email protected]', current_date(), 20000, 1);
ERROR 1644 (HY000): This email doesn't match [email protected]
mysql>