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.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
