Maintaining trees with triggers

[Chris Wood]

It seems in the present incarnation the usefulness of triggers is rather restricted. You can't use create temporary table for example, which makes doing some things quite difficult.

I'm trying to create a trigger to maintain a tree structure, the old parent, child, depth thing. Has anyone managed to do this with MySQL triggers? All my oracle scripts to do so rely on creating temporary tables. I know this is a fairly common thing for triggers to do, so I'm hoping that someone else has got a script for this already.

Here's the relivant bit of my layout:

CREATE TABLE `group` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(60) NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `group_group` (
`parent_group_id` int(10) unsigned NOT NULL,
`group_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`parent_group_id`,`group_id`),
KEY `FK_group_group_group` (`group_id`),
CONSTRAINT `FK_group_group_parent_group` FOREIGN KEY (`parent_group_id`) REFERENCES `group` (`id`) ON UPDATE CASCADE,
CONSTRAINT `FK_group_group_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `group_group_tree` (
`parent_group_id` int(10) unsigned NOT NULL,
`group_id` int(10) unsigned NOT NULL,
`depth` int(10) unsigned NOT NULL,
PRIMARY KEY (`parent_group_id`,`group_id`,`depth`),
KEY `FK_group_group_group` (`group_id`),
CONSTRAINT `FK_group_group_tree_parent_group` FOREIGN KEY (`parent_group_id`) REFERENCES `group` (`id`) ON UPDATE CASCADE,
CONSTRAINT `FK_group_group_tree_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The foreign key checks here ensure that groups can't get deleted without cleaning out their heirachies first. This is required since triggers won't get fired for cascades.



Edited 1 time(s). Last edit at 03/02/2006 03:08AM by Chris Wood.