Member Avatar for Daron_1

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
  • 353 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Daron_1

Dani AI

Generated

Short answer: the trigger must be created at top level (not inside a stored procedure). MySQL raises an error when a CREATE TRIGGER is executed from within another stored routine (error 1303 / ER_SP_NO_RECURSIVE_CREATE). ’s “I figured it out” is consistent with that rule — the fix is to define a BEFORE INSERT trigger directly and validate the incoming row there. (dev.mysql.com)

A minimal, working pattern (create the trigger outside any procedure; use NEW.Email and a regex; SIGNAL a user error) looks like this:

DELIMITER //
CREATE TRIGGER Review_Email_Before
BEFORE INSERT ON Faculty
FOR EACH ROW
BEGIN
  IF NEW.Email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email format';
  END IF;
END;
//
DELIMITER ;

This uses the row alias NEW inside a BEFORE trigger and the REGEXP operator to test the pattern; SIGNAL SQLSTATE '45000' is the normal way to raise a user-defined error. Adjust the regex to the desired format (and escape backslashes if embedding in strings). (dev.mysql.com)

Notes and alternatives: MySQL’s regex engine changed in 8.0, so prefer explicit character classes (like the example) for portability; REGEXP_LIKE() (8.0+) is another option. For schema-level enforcement, CHECK constraints (enforced starting with MySQL 8.0.16) can be used instead of triggers. For robust email validation, application-level checks or dedicated libraries are recommended — regexes in SQL can catch common mistakes but cannot fully guarantee a deliverable address. (dev.mysql.com)

Common pitfalls: forgetting DELIMITER when creating the trigger, testing the wrong column (must use NEW.Email), or trying to create the trigger inside a procedure (which triggers the 1303 error). If a trigger body needs shared logic, implement that logic in a stored procedure and CALL it from the trigger (observing trigger restrictions), rather than attempting to CREATE the trigger inside a routine. (dev.mysql.com)

Member Avatar for Daron_1

I figured it out!!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.