Re: Logging trigger activity and RAISE error
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 ;
Subject
Views
Written By
Posted
8156
October 18, 2011 09:26AM
2561
October 18, 2011 01:23PM
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.