Hi there,
could anyone adivse me a way to automate, via trigger, the deletion of table tuples on the delete event of this same table?
With the following trigger, I come up with a mutating table problem:
DROP TRIGGER IF EXISTS EXIST_DEP_DELETE_ITEM //
CREATE TRIGGER EXIST_DEP_DELETE_ITEM AFTER DELETE ON item
FOR EACH ROW
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE source_item INTEGER default 0 ;
DECLARE cur_1 CURSOR FOR SELECT relationship.item_source FROM item, relationship where item.item_id=relationship.item_target and
old.item_id=item.item_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO source_item;
DELETE FROM item WHERE item_id=source_item;
UNTIL done END REPEAT;
CLOSE cur_1;
END//
Here are my tables and data sample:
CREATE TABLE IF NOT EXISTS `item` (
`item_id` int(11) NOT NULL,
`value` varchar(5000) NOT NULL,
`parent_id` int(11) NOT NULL,
`version_id` int(11) NOT NULL,
PRIMARY KEY (`item_id`),
KEY `parent_id` (`parent_id`),
KEY `version_id` (`version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `relationship` (
`rel_id` int(11) NOT NULL AUTO_INCREMENT,
`item_target` int(11) NOT NULL,
`item_source` int(11) NOT NULL,
`version_id` int(11) NOT NULL,
PRIMARY KEY (`rel_id`),
KEY `item_target` (`item_target`),
KEY `item_source` (`item_source`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=220 ;
ALTER TABLE `relationship`
ADD CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`item_target`) REFERENCES `item` (`item_id`) ON DELETE CASCADE,
ADD CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`item_source`) REFERENCES `item` (`item_id`) ON DELETE CASCADE;
INSERT INTO `item` (`item_id`, `value`, `parent_id`, `version_id`) VALUES
(0, 'root', 0, 0),
(1, 'Role', 0, 0),
(3, 'Class', 0, 0),
(18, 'IS', 0, 0),
(19, 'IS_Role', 0, 0),
(20, 'Class_IS', 0, 0),
(24, 'Class_Role_IS', 0, 0);
INSERT INTO `relationship` (`rel_id`, `item_target`, `item_source`, `version_id`) VALUES
(22, 1, 19, 0),
(23, 18, 19, 0),
(24, 18, 20, 0),
(25, 3, 20, 0),
(34, 19, 24, 0),
(35, 20, 24, 0);
Many thanks for your support,
adnavv
Edited 1 time(s). Last edit at 01/27/2012 09:31AM by w o.