![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
![]() |
Wed, Jun 10 2009 10:36 AM | Permanent Link |
Peter Thorne | 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 |
Wed, Jun 10 2009 4:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Fri, Jun 12 2009 8:22 AM | Permanent Link |
"John Hay" | 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 |
Fri, Jun 12 2009 1:25 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Wed, Jun 17 2009 8:25 AM | Permanent Link |
Peter Thorne | Thanks John / Tim ...
|
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |