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

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

@r937, Do you think you can help me with my problem?

nope, sorry

There is no need to involve a stored procedure. Just create the trigger separately.

Some notes on the trigger code.

If you wish to refer to the email value just inserted, you should use

new.email

and not declare a local variable.

If you want to make a regular expression matching, use the regexp predicate. ( != only checks for inequality.)

https://dev.mysql.com/doc/refman/5.7/en/regexp.html The pattern matching syntax is described at

@swampBoogie,

Thank you very much for your input, but I figure out a much simpler way to solving my problem AFTER RESEARCHING for hours ONLINE. This is the solution I came up with; however, I am still not happy that I couldn’t place the trigger inside of a stored procedure. I know you are thinking, why do this when the trigger is only necessary? I thought so too but I am missing something that I don’t know about stored procedures and triggers combined together? Here is my solution with just the trigger.


-- Create a trigger that will enforce that the Faculty's Email has to be
-- used in a standard way. 

USE College ;

DROP TRIGGER IF EXISTS  Faculty_Before_Insert; 

DELIMITER $$

        CREATE TRIGGER Faculty_Before_Insert
        BEFORE INSERT ON Faculty 
        FOR EACH ROW 
        
      BEGIN 
      
        IF NEW.Email NOT LIKE '%__@college.edu%' THEN 
				 SIGNAL SQLSTATE VALUE '45000'
				  SET MESSAGE_TEXT= '[table: Faculty]- email column is not valid';
		END IF ; 

      END $$

DELIMITER ;



INSERT INTO Faculty(LastName, FirstName, Email, HireDate, Salary, DepartmentID) 
              VALUES('Seals', 'Daron', 'bad@bad.bad', current_date(), 20000, 1); 
              
		
SELECT 
Faculty.LastName, 
Faculty.FirstName, 
Faculty.Email 
 FROM  Faculty 
 WHERE ID= last_insert_id(); 
 
 



@r937,

I figured it out! Thanks anyway!

@swampBoogie,

Never mind. I will just accept my own answer. It’s not possible to have a trigger inside of a stored procedure, but it’s possible to have a stored procedure called from within a trigger. But for my purposes, I won’t need anything but a trigger. Thanks for your help. Unless you could think of a better way of expanding on my answer or is it fine the way it is? Any other suggestions?

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.