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

    Angry Unanswered: Trigger: Record may exist, and may not

    Here's my current trigger.....

    CREATE TRIGGER WF_INSERT_TRIG
    AFTER INSERT ON WF_START
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    DECLARE cfid,cabid CHAR(10);
    FOR getnames AS
    SELECT WR_ITEM_ID AS #C
    FROM WF_ITEM_REL
    WHERE WS_INSTANCE_ID = N.WS_INSTANCE_ID
    DO
    SET cfid = #C;
    END FOR;
    FOR getname AS
    SELECT CF_CABINET_ID AS #CB
    FROM CABINET_FOLDER_REL
    WHERE CF_FOLDER_ID = cfid
    DO
    IF #CB IS NULL THEN
    set cabid = cfid;
    ELSE
    set cabid = #CB;
    END IF;
    END FOR;
    FOR getnames AS
    SELECT AF_TITLE AS #AFT
    FROM APP_FOLDER_TAB
    WHERE AF_FOLDERID = cabID
    DO
    INSERT INTO FMI_WF_POLICY_REL
    VALUES
    (N.WS_INSTANCE_ID,#AFT);
    END FOR;
    END
    GO

    My problem, I think, is the middle getnames.

    The scenario here is that I get an instance ID which represents a file object in our imaging server. There's a folder system set up to contain all of these file objects. If the file object is in any old sub-folder, then there's a record for it in CABINET_FOLDER_REL, however, if it resides directly underneath the top-most folder, there isn't a record for it in that table.

    So, if there is a record, then I need to get info from that record, if not, I use the info I already have from the first call.

    Question is, how do I code that middle part where there could be a record and there also could not be a record?

    TIA

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Trigger: Record may exist, and may not

    Here is one way in which to answer your question. You could combine the first SELECT and the 2nd SELECT as an outer join:
    SELECT CASE CF_CABINET_ID IS NULL THEN WR_ITEM_ID ELSE
    CF_CABINET_ID END AS #C
    FROM WF_ITEM_REL
    LEFT OUTER JOIN CABINET_FOLDER_REL
    ON CF_FOLDER_ID = WR_ITEM_ID
    WHERE WS_INSTANCE_ID = N.WS_INSTANCE_ID
    The CASE statement mimics what's stated in your IF statement.
    Another possibility is to take the above SELECT and refer to as a table in 3rd SELECT. This option merges 3 steps into 1.

    SELECT A.AF_TITLE AS #AFT
    FROM APP_FOLDER_TAB A,
    (SELECT CASE CF_CABINET_ID IS NULL THEN WR_ITEM_ID ELSE CF_CABINET_ID END AS #C
    FROM WF_ITEM_REL
    LEFT OUTER JOIN CABINET_FOLDER_REL ON CF_FOLDER_ID = WR_ITEM_ID
    WHERE WS_INSTANCE_ID = N.WS_INSTANCE_ID) X
    WHERE AF_FOLDERID = X.#C

    Originally posted by putts
    Here's my current trigger.....

    CREATE TRIGGER WF_INSERT_TRIG
    AFTER INSERT ON WF_START
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    DECLARE cfid,cabid CHAR(10);
    FOR getnames AS
    SELECT WR_ITEM_ID AS #C
    FROM WF_ITEM_REL
    WHERE WS_INSTANCE_ID = N.WS_INSTANCE_ID
    DO
    SET cfid = #C;
    END FOR;
    FOR getname AS
    SELECT CF_CABINET_ID AS #CB
    FROM CABINET_FOLDER_REL
    WHERE CF_FOLDER_ID = cfid
    DO
    IF #CB IS NULL THEN
    set cabid = cfid;
    ELSE
    set cabid = #CB;
    END IF;
    END FOR;
    FOR getnames AS
    SELECT AF_TITLE AS #AFT
    FROM APP_FOLDER_TAB
    WHERE AF_FOLDERID = cabID
    DO
    INSERT INTO FMI_WF_POLICY_REL
    VALUES
    (N.WS_INSTANCE_ID,#AFT);
    END FOR;
    END
    GO

    My problem, I think, is the middle getnames.

    The scenario here is that I get an instance ID which represents a file object in our imaging server. There's a folder system set up to contain all of these file objects. If the file object is in any old sub-folder, then there's a record for it in CABINET_FOLDER_REL, however, if it resides directly underneath the top-most folder, there isn't a record for it in that table.

    So, if there is a record, then I need to get info from that record, if not, I use the info I already have from the first call.

    Question is, how do I code that middle part where there could be a record and there also could not be a record?

    TIA

  3. #3
    Join Date
    Jul 2003
    Location
    Frankenmuth, MI
    Posts
    21
    mac,
    thanks for your help so far.

    My current code now looks like this:
    <code>
    CREATE TRIGGER WF_INSERT_TRIG
    AFTER INSERT ON WF_START_DEVL
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    DECLARE CFID varchar(11);
    DECLARE AFID varchar(11);
    FOR getnames AS
    SELECT CT_ID AS #C
    FROM FOLDER_OBJECT_REL
    WHERE MD_ID=
    (SELECT WR_ITEM_ID
    FROM WF_ITEM_REL
    WHERE CHAR(WS_INSTANCE_ID,11) = CHAR(N.WS_INSTANCE_ID,11))
    DO
    SET CFID = #C;
    END FOR;
    FOR GETNAMES AS
    SELECT AF_FOLDERKEY AS #AFT
    FROM APP_FOLDER_TAB
    WHERE AF_FOLDERID = CFID
    OR
    AF_FOLDERID =
    (SELECT CF_PARENT_ID
    FROM CABINET_FOLDER_REL
    WHERE CF_FOLDER_ID = CFID)
    DO
    SET AFID = #AFT;
    END FOR;
    INSERT INTO FMI_WF_POLICY_REL
    VALUES
    (N.WS_INSTANCE_ID,AFID);
    END
    </code>

    What I need is a better way to compare the two WS_INSTANCE_ID's because that is what is failing.

    If I let it run and grab the N.WS_INSTANCE_ID that gets inserted into the target table, I can run all the proper queries and get the info I'm looking for, but for some reason, inside the trigger, the two ID's don't match so none of that info is grabbed.

    As you can see, right now I'm using a CHAR(value,11) to format them because both of those fields are CHAR(11)'s in their tables. This still isn't cutting it.

    If you could tell me a better technique to use to compare these values, that might fix me up.

    Thanks again.

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    Hey, is n.ws_instance_id required/populated (not null when trigger fired)? Can I see a sample of data for the fields referenced by the trigger? You should not have to cast a data type on a column that is already of that data type (stating the obvious).

    Also, is the end result that 1 row is inserted into fmi_wf_policy_rel? If multiple rows are supposed to be inserted into this table then the insert needs to be wrapped within the FOR and you can take the 2nd SELECT, put it in the first FOR, and remove the 2nd FOR.

    Not sure if I'm straying off-course.

    Originally posted by putts
    mac,
    thanks for your help so far.

    My current code now looks like this:
    <code>
    CREATE TRIGGER WF_INSERT_TRIG
    AFTER INSERT ON WF_START_DEVL
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    DECLARE CFID varchar(11);
    DECLARE AFID varchar(11);
    FOR getnames AS
    SELECT CT_ID AS #C
    FROM FOLDER_OBJECT_REL
    WHERE MD_ID=
    (SELECT WR_ITEM_ID
    FROM WF_ITEM_REL
    WHERE CHAR(WS_INSTANCE_ID,11) = CHAR(N.WS_INSTANCE_ID,11))
    DO
    SET CFID = #C;
    END FOR;
    FOR GETNAMES AS
    SELECT AF_FOLDERKEY AS #AFT
    FROM APP_FOLDER_TAB
    WHERE AF_FOLDERID = CFID
    OR
    AF_FOLDERID =
    (SELECT CF_PARENT_ID
    FROM CABINET_FOLDER_REL
    WHERE CF_FOLDER_ID = CFID)
    DO
    SET AFID = #AFT;
    END FOR;
    INSERT INTO FMI_WF_POLICY_REL
    VALUES
    (N.WS_INSTANCE_ID,AFID);
    END
    </code>

    What I need is a better way to compare the two WS_INSTANCE_ID's because that is what is failing.

    If I let it run and grab the N.WS_INSTANCE_ID that gets inserted into the target table, I can run all the proper queries and get the info I'm looking for, but for some reason, inside the trigger, the two ID's don't match so none of that info is grabbed.

    As you can see, right now I'm using a CHAR(value,11) to format them because both of those fields are CHAR(11)'s in their tables. This still isn't cutting it.

    If you could tell me a better technique to use to compare these values, that might fix me up.

    Thanks again.

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

    There's an order of inserts that run that I was not made aware of....
    A record gets inserted into WF_START
    Then a coorelating one into WF_ITEM_REL
    Then a coorelating one into FOLDER_OBJECT_REL

    So, when I was running my queries there was no data YET for them to match up with.

    Got it all working now, and thanks for all your help with this.

Posting Permissions

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