Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    46

    Unanswered: Trigger won't perform delete

    DB2 9.7 FP 8 LUW

    Hi all, I am having a small issue with my trigger. This is a after insert or delete trigger. I have if then statements to recognize the action being performed. The insert trigger works, and also the update in the delete logic works but the delete it self does not work. I have highlighted red the statements which did not work. So basically if REP table has only one record of person_id,person_region_id, and skill_id and it is being deleted - it should also delete from CLN. which is not happening. Please provide some expert help if you understand clearly the issue.

    When i ran the following:
    select count(*) from demo.js_skill_detail_rep where person_id=1007618505 and skill_id=196;
    1 was returned. then i ran the following to delete it:
    delete from demo.js_skill_detail_rep where person_id=1007618505 and skill_id=196;
    it deleted one record and it should've deleted the same from CLN. Then i ran:
    select * from demo.js_skill_detail_cln where person_id=1007618505 and skill_id=196;
    a row was returned. the row which was supposed to be deleted.

    CREATE OR REPLACE TRIGGER DEMO.JS_SKILL_DETAIL_REP_AIDR_TR
    AFTER INSERT OR DELETE
    ON DEMO.JS_SKILL_DETAIL_REP
    REFERENCING
    OLD AS OLD
    NEW AS NEW
    FOR EACH ROW
    BEGIN

    IF INSERTING THEN

    MERGE INTO DEMO.JS_SKILL_DETAIL_CLN AS C
    USING (SELECT MAX(INT(SCORE_VAL)) SCORE_VAL
    FROM DEMO.JS_SKILL_DETAIL_REP AS S
    WHERE S.PERSON_ID=NEW.PERSON_ID
    AND S.PERSON_REGION_ID=NEW.PERSON_REGION_ID
    AND S.SKILL_ID=NEW.SKILL_ID) AS X
    ON (C.PERSON_ID=NEW.PERSON_ID
    AND C.PERSON_REGION_ID=NEW.PERSON_REGION_ID
    AND C.SKILL_ID=NEW.SKILL_ID)
    WHEN MATCHED THEN
    UPDATE SET C.SCORE_VAL = X.SCORE_VAL
    WHEN NOT MATCHED THEN
    INSERT (PERSON_ID,PERSON_REGION_ID,SKILL_ID,SCORE_VAL)
    VALUES (NEW.PERSON_ID,NEW.PERSON_REGION_ID,NEW.SKILL_ID,N EW.SCORE_VAL);

    ELSEIF DELETING THEN

    MERGE INTO DEMO.JS_SKILL_DETAIL_CLN AS C
    USING (SELECT MAX(INT(S.SCORE_VAL)) AS SCORE_VAL
    FROM DEMO.JS_SKILL_DETAIL_REP AS S
    WHERE S.PERSON_ID=OLD.PERSON_ID
    AND S.PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND S.SKILL_ID=OLD.SKILL_ID
    GROUP BY S.PERSON_ID,S.PERSON_REGION_ID,S.SKILL_ID) AS X
    ON (C.PERSON_ID=OLD.PERSON_ID
    AND C.PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND C.SKILL_ID=OLD.SKILL_ID)

    WHEN MATCHED AND C.SCORE_VAL < INT(OLD.SCORE_VAL) THEN
    UPDATE SET C.SCORE_VAL = X.SCORE_VAL
    WHEN MATCHED AND C.SCORE_VAL = INT(OLD.SCORE_VAL) THEN
    DELETE;


    END IF;
    END;
    Last edited by jigmod01; 11-01-13 at 15:21.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    So basically if REP table has only one record of person_id,person_region_id, and skill_id and it is being deleted - it should also delete from CLN.
    How the requirement related with the condition?
    WHEN MATCHED AND C.SCORE_VAL = INT(OLD.SCORE_VAL) THEN
    There might be hidden/implicit conditions/relationships in SCORE_VAL of CLN and SCORE_VALof REP.

    Didn't I missed the point?

  3. #3
    Join Date
    Sep 2013
    Posts
    46
    The condition is (c.person_id=old.person_id, c.person_region_id=old.person_region_id, c.skill_id=old.skill_id, c.score_val=int(old.score_val))

    Target values should be deleted whenever they match the values being deleted from source (OLD). I didn't mean to say the last value, I said last because if it isn't the last then most likely an update will happen since we're searching for the record with max(score_val).

    Hope that makes sense...

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    OLD.SCORE_VAL is in REP table and C.SCORE_VAL is in CLN table.
    You didn't mentioned explicitly the relationship between CLN.SCORE_VAL and REP.SCORE_VAL.
    So, comparing C.SCORE_VAL and OLD.SCORE_VAL was meaningless for me.

    Even if you updated C.SCORE_VAL by
    WHEN MATCHED AND C.SCORE_VAL < INT(OLD.SCORE_VAL) THEN
    UPDATE SET C.SCORE_VAL = X.SCORE_VAL
    Suppose if you deleted REP from higher SCORE_VAL(or last deleted REP.SCORE_VAL was not largest one ever presented),
    CLN.SCORE_VAL(at the time of deleting last REP) might be not equal to the last REP.SCORE_VAL.
    (This is one possible scenario. Other scenarios might be thinkable.)

    Why didn't you use COUNT(*) in USING subquery?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Removed due to double post.

Tags for this Thread

Posting Permissions

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