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