0

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:

-- 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(); 
1
Contributor
1
Reply
19
Views
7 Months
Discussion Span
Last Post by Daron_1
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.