[sqlite] Trigger and Tree Problem

[Thorsten Kortmann]

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