Not sure if there's a way with your current code, but if you use either an LTREE or nested sets you'd be able to detect this situation quite easily. There was a discussion about both on the list about 2 weeks ago.
On Thu, Feb 23, 2006 at 05:13:37PM +0100, Thorsten Kortmann wrote: > sorry for my english.. > > i have two tables: > > 1. GROUP > id > description > is_subgroup > > 2. GROUPDETAIL > id > groups_id > remote_id > remote_type > > - each group can have 1 to x groupdetail. > - groupdetail.remote_type can have two values, 0 and 1 > if it is 0, remote_id points to a product > if it is 1, remote_id points to a group (where is_subgroup=1) > > The is_subgroup problem is fixed by this trigger: > I only can point to (sub)groups. > > CREATE TRIGGER bupdate_groupdetail BEFORE UPDATE ON groupdetail > FOR EACH ROW BEGIN > SELECT RAISE(ROLLBACK, 'error...') > WHERE new.remote_type = 1 > AND (SELECT id FROM group WHERE id = new.remote_id > AND is_subgroup = 1) IS NULL; > END; > > But i can point to (sub)groups that allready part of the same path. > In this way i get an endless recursion (1). > > GROUP-->SUB-->SUB-->SUB-->PRODUCT > | | | > +-<-1-+ +--->SUB-->PRODUCT > > Is there a way to check all parent (sub)groubs inside a trigger? > > HTH > Thorsten > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
