MySQL Forums
Forum List  »  Triggers

Re: Logging trigger activity and RAISE error
Posted by: David Harbeson
Date: October 19, 2011 11:57AM

I figured out the problem. My trigger_log table was an InnoDB table. I switched it to MyISAM, and my insert worked (apparently, did not rollback). However, I could not use a variable to set my MESSAGE_TEXT in the SIGNAL statement. So, I got rid of my "sub-routine" stored procedures and ended up with a somewhat messy trigger....
======================================================================
DELIMITER $$
CREATE TRIGGER bd_department_fer BEFORE DELETE ON department
FOR EACH ROW
BEGIN
DECLARE check_count INT;
DECLARE error_name VARCHAR(64) DEFAULT 'bd_department_fer';
DECLARE error_field VARCHAR(128) DEFAULT '';
DECLARE generic_error CONDITION FOR SQLSTATE '45000';

SET error_field=CONCAT('department_id=',OLD.department_id);

SET check_count=(SELECT COUNT(*) FROM hierarchy_node hn WHERE hn.child_id=OLD.department_id AND hn.child_type=4);
IF (check_count > 0) THEN
INSERT INTO trigger_log (trigger_name, trigger_field, trigger_error_text) VALUES (error_name,error_field,
'Cannot delete department because it is a child_id in the hierarchy_node table with a child_type=4');
SIGNAL generic_error SET MESSAGE_TEXT=
'Cannot delete department because it is a child_id in the hierarchy_node table with a child_type=4';
END IF;
END; $$
DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Logging trigger activity and RAISE error
2298
October 19, 2011 11:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.