MySQL Forums
Forum List  »  Triggers

Delete trigger for deleting same table tuples
Posted by: w o
Date: January 27, 2012 08:33AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Delete trigger for deleting same table tuples
4407
w o
January 27, 2012 08:33AM


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.