Logging trigger activity and RAISE error
I've got a trigger that enforces business rules (I can't use a foreign key in this case), and I want it to log the fact that it fired. I have two stored procedures that: 1-insert to a log file and 2-raise an error. No matter what I've tried, I cannot get it to insert to the log file. It seems that the raise does some form of rollback.
Is there any work around for this?
========
CREATE TRIGGER bd_department_fer BEFORE DELETE ON department
FOR EACH ROW
BEGIN
DECLARE error_name VARCHAR(64) DEFAULT 'bd_department_fer';
DECLARE error_field VARCHAR(256) DEFAULT '';
DECLARE error_text VARCHAR(1024) DEFAULT '';
DECLARE check_count INT;
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
SET error_text='Cannot delete deparment record because it exists in the
hierarchy_node table';
CALL usp_insertTriggerLog(error_name,error_field,error_text);
CALL usp_raiseException(error_text);
END IF;
END; $$
========
DROP PROCEDURE IF EXISTS `usp_insertTriggerLog`; $$
CREATE PROCEDURE `usp_insertTriggerLog`(IN _name VARCHAR(64), IN _field VARCHAR(256), IN _text VARCHAR(1024) )
SQL SECURITY INVOKER
BEGIN
INSERT INTO trigger_log (`trigger_name`, `trigger_field`, `trigger_error_text`) VALUES (_name,_field,_text);
END $$
========
DROP PROCEDURE IF EXISTS `usp_raiseException`; $$
CREATE PROCEDURE `usp_raiseException`(IN _text VARCHAR(1024) )
SQL SECURITY INVOKER
BEGIN
DECLARE generic_error CONDITION FOR SQLSTATE '45000';
SIGNAL generic_error
SET MESSAGE_TEXT=_text;
END $$
Subject
Views
Written By
Posted
Logging trigger activity and RAISE error
8178
October 18, 2011 09:26AM
2570
October 18, 2011 01:23PM
2308
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.