Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Frankenmuth, MI
    Posts
    21

    Unhappy Unanswered: Trigger loop (file to folders relation)

    What I have is a 5 table database to relate files to folders in which they reside. (See attached image)

    Some of the table seem useless, but it's a mimic of an Imaging system we have in our company so that's why they're there.

    Basically, the top most folder that the just inserted file in has some information that I want to pull (along with the just inserted file's ID #) and put into an info table.

    The trick is the infinite # of sub folders that there can be.

    What I have so far is:
    DROP TRIGGER TEST_INSERT
    GO

    CREATE TRIGGER TEST_INSERT
    AFTER INSERT ON TEST_FILES
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    FOR getnames AS
    SELECT FOLDID AS #F, PARENTID AS #P
    FROM TEST_FOLDERS
    WHERE FOLDID =
    (SELECT FOLDID
    FROM TEST_PHYS_FOLDERS
    WHERE PHYSICALID =
    (SELECT PHYSICALID
    FROM TEST_PHYS
    WHERE FILEID = N.FILEID))
    DO
    WHILE #F <> #P DO
    SELECT FOLDID AS #F, PARENTID AS #P
    FROM TEST_FOLDERS
    WHERE FOLDID = #P;
    END WHILE;
    FOR getnames AS
    SELECT FOLDNAME AS #FN, CREATEDTE AS #CD
    FROM TEST_TOPMOST
    WHERE FOLDID = #F
    DO

    INSERT INTO TEST_REL
    VALUES(
    N.FILEID,#FN,#CD);
    END FOR;
    END FOR;
    END
    GO

    INSERT INTO TEST_FILES
    VALUES
    ('Alpha','Alpha File')
    GO


    This is my first attempt at a DB2 Trigger, so I'm not sure how to debug or anything.

    Any ideas will be greatly appreciated.
    Attached Thumbnails Attached Thumbnails db.bmp  

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Trigger loop (file to folders relation)

    Can you please mention the error message you receive and/or when you get it ...

    AND/OR

    Can you please include your table DDL(as an attachment preferably) and a sample INSERT Statement for TEST_FILES

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2003
    Location
    Frankenmuth, MI
    Posts
    21
    I can let the thing run for quite some time and not get any error.

    The DDL file is actually a copy of the text that the journal showed was in it as I dont have server access to get to the acutal DDL file, but I think it should work at any rate. Do you need the other 5 table's DDL's also?

    The insert statement is at the bottom of my first message.

  4. #4
    Join Date
    Jul 2003
    Location
    Frankenmuth, MI
    Posts
    21
    Oops, forgot the file.
    Attached Files Attached Files

  5. #5
    Join Date
    Jul 2003
    Location
    Frankenmuth, MI
    Posts
    21
    Figured it out.

    Switched the code around to use some variables to control the While.

    DROP TRIGGER TEST_INSERT
    GO

    CREATE TRIGGER TEST_INSERT
    AFTER INSERT ON TEST_FILES
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    DECLARE fid,pid CHAR(10);
    FOR getnames AS
    SELECT FOLDID AS #F, PARENTID AS #P
    FROM TEST_FOLDERS
    WHERE FOLDID=
    (SELECT FOLDID
    FROM TEST_PHYS_FOLDERS
    WHERE PHYSICALID =
    (SELECT PHYSICALID
    FROM TEST_PHYS
    WHERE FILEID=N.FILEID))
    DO
    SET fid = #F;
    SET pid = #P;
    END FOR;
    WHILE pid <> fid DO
    FOR getname AS
    SELECT FOLDID AS #F, PARENTID AS #P
    FROM TEST_FOLDERS
    WHERE FOLDID = pid
    DO
    SET fid = #F;
    SET pid = #P;
    END FOR;
    END WHILE;
    FOR getnames AS
    SELECT FOLDNAME AS #FN, CREATEDTE AS #CD
    FROM TEST_TOPMOST
    WHERE FOLDID = fid
    DO
    INSERT INTO TEST_REL
    VALUES
    (N.FILEID,#FN,#CD);
    END FOR;
    END
    GO

    INSERT INTO TEST_FILES
    VALUES
    ('Alpha','Alpha File')
    GO

    Working now!!!
    Thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •