<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

     
    -- 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(); 
     
     



<!-- end snippet -->

Hello Everyone, 
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: