Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Unanswered: DB2 Trigger Help

    Hi there,
    I am new to triggers and SP(never written an SP) but the 1st trigger on insert worked however the following does not work.

    CREATE TRIGGER VNAUSER.DEL_LOCATION
    AFTER INSERT OF LOCATION ON VNAUSER.PSHR1TEMP
    REFERENCING OLD AS OLD_TAB
    NEW AS NEW_TAB
    FOR EACH ROW MODE DB2SQL
    DELETE FROM VNAUSER.TBLLOCATION;
    INSERT INTO VNAUSER.TBLLOCATION(Location) select distinct location from VNAUSER.PSHR1TEMP;

    What I am trying to do with above Trigger is as soon as there in an insert in TBLPSHR1TEMP, the existing TBLLOCATION date should be deleted and a fresh data on Location should be added..However the risk that I realized is, if someone is accessing the TBLLocation.
    However there is solution which I am finding it difficult to incorporate.

    All I need to check is if there is a location in TBLPSHR1TEMP which is not there in TBLLOCATION and then its only an INSERT and NOT A DELETE of TBLLOCATION DATA

    kindly provide guidance as to this
    regards
    aba

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try the MERGE statement instead.

    Andy

  3. #3
    Join Date
    Jul 2010
    Posts
    2

    Thanks

    The MERGE seems to be a good option

Posting Permissions

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