I am working on creating a transaction rollback in which I created a stored procedure that either outputs whether the faculty member was added successfully or not added successfully to the faculty table, but the problem was that my code didn’t print out the error message that it needed to print out. I know it was not a successfully transaction because Faculty member Eduardo Egghead still had a registration record as a student. How can I solve this problem? However, I did come up with one solution and the solution was to get ready of the CONCAT function and just simply write in the error message using the SELECT statement just like I did for the commit part. Any other suggestions?
-- Create a Stored Procedure named Fix_Egghead1 that deletes Eduardo from Student table and insert -- him into the Faculty table USE COLLEGE; DROP PROCEDURE IF EXISTS Fix_Egghead1; DELIMITER $$ CREATE PROCEDURE Fix_Egghead1() BEGIN -- Setup error handing DECLARE errorOccurred INT DEFAULT FALSE; DECLARE errorMessage VARCHAR(255); -- Called when an error occurs DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET errorOccurred = TRUE; GET DIAGNOSTICS CONDITION 1 errorMessage = MESSAGE_TEXT; END ; START TRANSACTION; DELETE FROM Student WHERE Student.ID= 57; INSERT INTO Faculty(LastName, FirstName, Email, HireDate, Salary, DepartmentID) VALUES('Egghead', 'Eduardo', 'EduardoEgghead@College.edu', 115000, 1); IF errorOccurred = TRUE THEN ROLLBACK; SELECT CONCAT( 'The faculty member wasn't succesfully added to the Faculty table:' , errorMessage) AS Results; ELSE COMMIT; SELECT 'The faculty member was successfully added to the Faculty table.'; END IF; END $$ DELIMITER ; CALL Fix_Egghead1();