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

    Unanswered: DELETE trigger help

    DB2 9.7 FP 8 on unix:

    I have the following multi-event trigger:

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

    DECLARE V_MAX_SCORE_VAL INTEGER;

    SELECT MAX(B.INT_SCORE_VAL) AS MAX_INT_SCORE_VAL INTO V_MAX_SCORE_VAL
    FROM (
    SELECT INT(SCORE_VAL) AS INT_SCORE_VAL
    FROM DEMO.XYZ X
    WHERE X.PERSON_ID=OLD.PERSON_ID
    AND X.PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND X.SKILL_ID=OLD.SKILL_ID
    AND INT(X.SCORE_VAL) < INT(OLD.SCORE_VAL)
    ) B ;

    IF INSERTING THEN

    BEGIN ATOMIC
    MERGE INTO DEMO.TARGET AS C
    USING (SELECT MAX(INT(SCORE_VAL)) SCORE_VAL
    FROM DEMO.SOURCE 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 NOT MATCHED THEN
    INSERT (PERSON_ID,PERSON_REGION_ID,SKILL_ID,SCORE_VAL)
    VALUES (NEW.PERSON_ID,NEW.PERSON_REGION_ID,NEW.SKILL_ID,X .SCORE_VAL);

    END;

    ELSEIF DELETING THEN

    BEGIN ATOMIC

    MERGE INTO DEMO.TARGET AS C
    USING (SELECT S.PERSON_ID AS PERSON_ID, S.PERSON_REGION_ID AS PERSON_REGION_ID, S.SKILL_ID AS SKILL_ID, INT(S.SCORE_VAL) AS SCORE_VAL, COUNT(*)
    FROM DEMO.SOURCE 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 PERSON_ID,PERSON_REGION_ID,SKILL_ID,SCORE_VAL
    HAVING COUNT(*)>=1
    ) AS X
    ON (C.PERSON_ID=X.PERSON_ID
    AND C.PERSON_REGION_ID=X.PERSON_REGION_ID
    AND C.SKILL_ID=X.SKILL_ID
    )

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

    END;


    ELSEIF UPDATING THEN

    BEGIN ATOMIC


    MERGE INTO DEMO.TARGET AS C
    USING (SELECT S.PERSON_ID PERSON_ID, S.PERSON_REGION_ID PERSON_REGION_ID, S.SKILL_ID SKILL_ID, INT(S.SCORE_VAL) SCORE_VAL
    FROM DEMO.SOURCE AS S
    WHERE PERSON_ID=NEW.PERSON_ID
    AND PERSON_REGION_ID=NEW.PERSON_REGION_ID
    AND SKILL_ID=NEW.SKILL_ID
    AND INT(SCORE_VAL)=INT(NEW.SCORE_VAL)) AS X
    ON (C.PERSON_ID=X.PERSON_ID
    AND C.PERSON_REGION_ID=X.PERSON_REGION_ID
    AND C.SKILL_ID=X.SKILL_ID
    AND (INT(C.SCORE_VAL) < X.SCORE_VAL OR C.SCORE_VAL IS NULL))

    WHEN MATCHED THEN
    UPDATE SET C.SCORE_VAL = X.SCORE_VAL;

    END;
    END IF;
    END;

    The delete logic is in bold. I keep running into error while trying this. all i want to do it only delete from target if the last record is delete from source columns (person_id,person_region_id,skill_id,score_val)

    I tried to use count(*) to count the occurrences and if it = 1 then delete from target.

    Any ideas?

  2. #2
    Join Date
    Sep 2013
    Posts
    46
    So actually the main issue i dont understand is when i do this:

    ELSEIF DELETING THEN

    BEGIN ATOMIC

    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) 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 X.SCORE_VAL > INT(C.SCORE_VAL) THEN
    UPDATE SET C.SCORE_VAL = X.SCORE_VAL

    WHEN NOT MATCHED THEN
    DELETE;


    ^This bold part isn't acceptable, but really that's when i need to delete it from the target - when those values don't exist in source anymore.

    Is it not possible to use WHEN NOT MATCHED THEN DELETE ???

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What were the difference from this issue?
    http://www.dbforums.com/db2/1697837-...rm-delete.html


    Anyway,
    you can't DELETE, WHEN NOT MATCHED in a MERGE statement.

    Please see manuals, like in
    MERGE - IBM DB2 9.7 for Linux, UNIX, and Windows
    NOT MATCHED
    Indicates the operation to be performed on the rows where the ON search condition is false or unknown. Only INSERT or signal-statement can be specified after THEN.
    Last edited by tonkuma; 11-15-13 at 14:09.

  4. #4
    Join Date
    Sep 2013
    Posts
    46
    The difference is that one deleted even if there was another dupe in source. Now it has been changed to delete from target only if the last record is deleted from source. Although I wish I could've figured this out earlier I have finally working code. I used an IF/THEN statement instead of MERGE. For formality of sharing and closing issue pfb code. This one also is an AFTER INSERT OR UPDATE OR DELETE trigger:

    CREATE OR REPLACE TRIGGER SOURCE_AIUD_TR
    AFTER INSERT OR DELETE OR UPDATE
    ON SOURCE
    REFERENCING
    NEW AS NEW
    OLD AS OLD
    FOR EACH ROW

    BEGIN

    DECLARE V_MAX_VAL INTEGER;

    IF INSERTING THEN

    BEGIN ATOMIC
    MERGE INTO TARGET AS C
    USING (SELECT MAX(INT(VAL)) AS VAL
    FROM SOURCE 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 NOT MATCHED THEN
    INSERT (PERSON_ID,PERSON_REGION_ID,SKILL_ID,VAL)
    VALUES (NEW.PERSON_ID,NEW.PERSON_REGION_ID,NEW.SKILL_ID,N EW.VAL);

    END;

    ELSEIF DELETING THEN

    BEGIN ATOMIC

    SELECT MAX(INT(VAL)) INTO V_MAX_VAL
    FROM SOURCE
    WHERE PERSON_ID=OLD.PERSON_ID
    AND PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND SKILL_ID=OLD.SKILL_ID;

    IF NOT EXISTS (SELECT S.PERSON_ID, S.PERSON_REGION_ID, S.SKILL_ID
    FROM SOURCE S
    WHERE S.PERSON_ID=OLD.PERSON_ID
    AND S.PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND S.SKILL_ID=OLD.SKILL_ID)

    THEN DELETE FROM TARGET C
    WHERE C.PERSON_ID=OLD.PERSON_ID
    AND C.PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND C.SKILL_ID=OLD.SKILL_ID;

    ELSE UPDATE TARGET AS C SET C.VAL=V_MAX_VAL
    WHERE C.PERSON_ID=OLD.PERSON_ID
    AND C.PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND C.SKILL_ID=OLD.SKILL_ID;

    END IF;

    END;

    ELSEIF UPDATING THEN

    SELECT MAX(INT_VAL) INTO V_MAX_VAL
    FROM (
    SELECT S.PERSON_ID,S.PERSON_REGION_ID,S.SKILL_ID, INT(S.VAL) AS INT_VAL FROM SOURCE 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);

    BEGIN ATOMIC

    MERGE INTO TARGET AS C
    USING (
    VALUES(NEW.PERSON_ID,NEW.PERSON_REGION_ID,NEW.SKIL L_ID) )
    AS X(PERSON_ID,PERSON_REGION_ID,SKILL_ID)
    ON (C.PERSON_ID=NEW.PERSON_ID
    AND C.PERSON_REGION_ID=NEW.PERSON_REGION_ID
    AND C.SKILL_ID=NEW.SKILL_ID
    AND INT(C.VAL) < INT(NEW.VAL))

    WHEN MATCHED THEN
    UPDATE SET C.VAL = V_MAX_VAL;

    END;
    END IF;
    END;

Posting Permissions

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