I have a simple EDB table containg the self-refencing structure for a tree. When I delete a row that represents the middle of a branch, I want to make sure
that all orphans along the remains of the branch are also deleted. Can any one think of a tidy way to do this? My curent trigger

TRIGGER "after_delete"
BEGIN
 DECLARE Structures_stmnt STATEMENT;
 PREPARE Structures_stmnt FROM 'DELETE FROM Structures WHERE Structures.parent_id <> 0 AND Structures.parent_id NOT IN (SELECT item_id FROM
Structures)';
 EXECUTE Structures_stmnt;
END

deletes the immediate orphan but does not appear to fire again when this itself is deleted (i.e. it doesn't nest) so I am left with spurious entries in my table.

Thanks

Peter

Peter,

<< deletes the immediate orphan but does not appear to fire again when this
itself is deleted (i.e. it doesn't nest) so I am left with spurious entries
in my table. >>

You should use the OLD_ROW row values to drive the DELETE.  That should fix
the problem for you.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim/Peter

> << deletes the immediate orphan but does not appear to fire again when
this
> itself is deleted (i.e. it doesn't nest) so I am left with spurious
entries
> in my table. >>
>
> You should use the OLD_ROW row values to drive the DELETE.  That should
fix
> the problem for you.

I gave this a quick try and it seems like the "recursive" delete does not
fire the delete trigger.  A workaround using the RowExists function you
created in a previous post might be

CREATE FUNCTION "RowExists" (IN "SQLToExecute" VARCHAR)
RETURNS BOOLEAN
BEGIN
  DECLARE TempCursor CURSOR FOR TempStmt;

  PREPARE TempStmt FROM SQLToExecute;
  OPEN TempCursor;
  RETURN ROWCOUNT(TempCursor) > 0;
END

TRIGGER "after_delete"
BEGIN
 DECLARE Structures_stmnt STATEMENT;

 WHILE ROWEXISTS('SELECT  *  FROM Structures WHERE Structures.parent_id <>
0 AND Structures.parent_id NOT IN (SELECT item_id FROM Structures)') DO
   PREPARE Structures_stmnt FROM 'DELETE FROM Structures WHERE
Structures.parent_id <> 0 AND Structures.parent_id NOT IN (SELECT item_id
FROM Structures)';
   EXECUTE Structures_stmnt;
 END WHILE;
END

John

John,

<< I gave this a quick try and it seems like the "recursive" delete does not
fire the delete trigger.  A workaround using the RowExists function you
created in a previous post might be >>

It should - I'll check it out and let you know what I find.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks John / Tim ...