Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2007
    Posts
    27

    Unanswered: Create a trigger to insert or update a table

    Hi guys!

    Could any one help me to create a trigger that has a cursor on it to identify if new data i will insert but if a data has been insert already i will just update?

    Thanks,
    Life is short.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    In DB2 9, just use "MERGE" for this.
    If you are using an older version of DB2, you could try to create a "before" trigger to catch the insert, but I don't see how you could make it do the update instead, since the before trigger will have to induce a negative SQLCODE hence no changes are performed; and otherwise the INSERT fails and again no changes are performed.
    The only solution I see is creating a stored procedure which contains the following sequential logic:
    - test whether the PK is already present, e.g. by doing the insert and checking the SQLCODE.
    - do the insert when the PK was not yet present
    - do any sort of UPDATE instead when the PK is present.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    consider using an update-statement and an "AFTER UPDATE"-trigger which inserts the data if the primary key is not found.
    But - as Peter.Vanroose already mentioned - a stored procedure might be the better alternative.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    "merge" is also available in v8. This was one of new functionalities of v8.
    Hope this helps,
    Grofaty

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by grofaty
    "merge" is also available in v8. This was one of new functionalities of v8.
    Only in v8 for LUW -- v8 for z/OS does not have MERGE, but version 9 has.
    (It was not clear from the original posting which platform this was referring to.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jun 2007
    Posts
    27
    CREATE TRIGGER ADMINISTRATOR.WAFERINSERT NO CASCADE BEFORE INSERT ON ADMINISTRATOR.SLDR_RAW1 REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
    WHEN(EXISTS
    (SELECT lot_no,waferrow,class,jobnum,op,txnts,userid,tool, product
    FROM ADMINISTRATOR.SLDR_RAW1
    WHERE lot_no = NEW.lot_no
    AND waferrow = NEW.waferrow
    AND class = NEW.class
    AND op = NEW.op
    AND tool = NEW.tool
    AND product = NEW.product))
    BEGIN ATOMIC
    SET txnts = NEW.txnts;
    SET good = NEW.good;
    SET product = NEW.product;
    SET tool = NEW.tool;
    SET userid = NEW.userid;
    END

    This is what i've made but i didn't work at all.
    I use db2 import to send csv file
    mainly i will do this regular insertion about 300,000 rows a day.
    but if i will use a procedure i think it would be slow

    Can you give me suggestion?

    Thanks,
    mabeljovan

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by mabeljovan
    Can you give me suggestion?
    If you are on v8 for Linux/Unix/Windows, I would suggest you to use MERGE.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Jun 2007
    Posts
    27
    Thanks,

    But can I use merge for a single table only?

    Regards,
    mabeljovan

  9. #9
    Join Date
    Jun 2007
    Posts
    27
    Could i use it on a trigger/procedure/function or a script?

    Regards,
    mabeljovan

Posting Permissions

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