Mysql – How to properly use a trigger inside of stored procedure to validate an email

MySQLstored-procedurestrigger

-- 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 FirstLast@college.edu'; 
        END IF ; 

      END;

END
$$

DELIMITER ;



INSERT INTO Faculty(LastName, FirstName, Email, HireDate, Salary, DepartmentID) 
              VALUES('Stanley', 'Mike', 'bad@bad.bad', 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:

Best Answer

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 FirstLast@college.edu';
    ->         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', 'bad@bad.bad', current_date(), 20000, 1);
ERROR 1644 (HY000): This email doesn't match FirstLast@college.edu
mysql>
Related Question