Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    7

    Unanswered: Matching column names with data

    Hi Everyone,
    I'm trying to create a trigger that will insert the newly inserted record into a single column of a seperate table in the form of key value pairs. I have many tables that I am tracking inserts for so I want to insert something in a single column of the history table that would record the data in key=value form.

    For example:

    TableA with columns Col1 and Col2.
    HistoryTable with columns Date, Operation, ins_del_values

    When a record is inserted into TableA the history record would look like:
    09/08/2008 Insert Col1=ABC,Col2=XYZ

    I have the following which is okay except I would like to not hard code the mapping of keys (column names) with values (inserted values).

    CREATE TRIGGER INS_TABLEA
    AFTER INSERT ON TABLEA
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    DECLARE values VARCHAR(4000);
    SET values = 'COL1=' CONCAT(N.COL1)
    CONCAT(',COL2=') CONCAT(N.COL2);
    INSERT INTO HISTORY_TBL (DATE_CHANGED, OPERATION, INS_DEL_VALUES_LIST)
    VALUES (CURRENT TIMESTAMP, 'INSERT', values);
    END#

    Is there an easier way to do this?

    Thanks for you help.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Yadrif
    Is there an easier way to do this?
    No, I would do it exactly like this.
    Maybe just write it a bit more condensed as follows:
    Code:
    CREATE TRIGGER INS_TABLEA
    AFTER INSERT ON TABLEA
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    INSERT INTO HISTORY_TBL (DATE_CHANGED, OPERATION, INS_DEL_VALUES_LIST)
    VALUES (CURRENT TIMESTAMP, 'INSERT', 'COL1='||N.COL1||',COL2='||N.COL2)
    #
    The "current timestamp" could further be avoided by having a "WITH DEFAULT" on the "DATE_CHANGED" column ("ALTER TABLE history_tbl ALTER COLUMN date_changed SET DEFAULT"):
    Code:
    INSERT INTO HISTORY_TBL (OPERATION, INS_DEL_VALUES_LIST)
    VALUES ('INSERT', 'COL1='||N.COL1||',COL2='||N.COL2)
    Last edited by Peter.Vanroose; 09-08-08 at 15:07.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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