Loops in Triggers

[putts]

I'm pretty new to doing triggers in DB2, and I have a fairly in depth one that I'm trying to figure out if it's possible or not.

What happens is each record inserted into this one table actually represents a file. In another table is stored all the folders that that file might reside inside of. The top most folders have their own table as well as being in the folders table. This top most table stores some information that I'm trying to pull out and store into a different table along with the file's ID # that I just inserted.

The trick is this - the table with all the sub folders has two fields in it that you use to figure out if the current folder is the top most one, or just another sub folder.

So, what I need to do is get the ID of the file just inserted - got that figured out.
Then, I have to use that to get the folder it's in - got that too.
Then, I have to use that folder's ID, figure out if it's a top-most folder or not - got that too.
But then, If it's not the top-most, I have to figure out if the parent of that folder's is the top-most, if not, then I have figure out if the parent ........... and on and on.

So, how do I do this loop over and over again?

 

[ARWinner]

What you need is to do the query recursively.

There is a cookbook for DB2 located at:

http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM

that has how to do recursion.

HTH

Andy