troubleshooting Question

Customize exception message in MySQL

Avatar of softbless
softbless asked on
PHPMySQL Server
6 Comments4 Solutions721 ViewsLast Modified:
Hi Guys,

I have a trigger that prevent insert in 1 table with some condition. I use it for my web application.

The problem is when the trigger is running and prevent insert, it show error message from MySQL (an exception).

My question : how to make the error/exception message to be user friendly in my PHP web application?

Now the error message is : Query Failed:INSERT into acl_roles_users (role_id,user_id,deleted,date_modified,id) VALUES ('36e1b9aa-bfd6-4855-f7d8-4c610b9074fa','b60a794c-ea95-472c-86ee-4c7887ab048b','0','2010-10-01 04:46:07','3a3711e6-f290-9809-cc6e-4ca567d955f3')::MySQL error 1415: Not allowed to return a result set from a trigger

I want the exception message to be : 'The record cannot be assigned to 1 role

I attach my trigger script.
DROP PROCEDURE IF EXISTS raise_application_error_insert_otherrole_rolesoc;
DROP PROCEDURE IF EXISTS get_last_custom_error_insert_otherrole_rolesoc;
DROP TABLE IF EXISTS RAISE_ERROR_insert_otherrole_rolesoc;

DELIMITER $$
CREATE PROCEDURE raise_application_error_insert_otherrole_rolesoc(IN CODE INTEGER, IN MESSAGE VARCHAR(255)) SQL SECURITY INVOKER DETERMINISTIC
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS RAISE_ERROR_insert_otherrole_rolesoc(F1 INT NOT NULL);

  SELECT CODE, MESSAGE INTO @error_code, @error_message;
  INSERT INTO RAISE_ERROR_insert_otherrole_rolesoc VALUES(NULL);
END;
$$

CREATE PROCEDURE get_last_custom_error_insert_otherrole_rolesoc() SQL SECURITY INVOKER DETERMINISTIC
BEGIN
  SELECT 'User with RoleSOC cannot be assigned to other role' ;

END;
$$
DELIMITER ;


DROP trigger   prevent__insert_otherrole_rolesoc;


DELIMITER $$
CREATE TRIGGER prevent__insert_otherrole_rolesoc_edited
   BEFORE insert    ON acl_roles_users 
   FOR EACH ROW
BEGIN
if EXISTS (select * from acl_roles_users where role_id='1196cdbd-ddd6-d49d-bf44-4c784fe3b3c7' and user_id=new.user_id) THEN
   CALL get_last_custom_error_insert_otherrole_rolesoc();
END IF;
END$$
DELIMITER ;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 4 Answers and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros