Thread: Matching column names with data
09-08-08, 14:54 #1Registered User
- Join Date
- Sep 2008
Unanswered: Matching column names with data
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.
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
DECLARE values VARCHAR(4000);
SET values = 'COL1=' CONCAT(N.COL1)
INSERT INTO HISTORY_TBL (DATE_CHANGED, OPERATION, INS_DEL_VALUES_LIST)
VALUES (CURRENT TIMESTAMP, 'INSERT', values);
Is there an easier way to do this?
Thanks for you help.
09-08-08, 15:04 #2Registered User
Originally Posted by Yadrif
- Join Date
- Sep 2004
Maybe just write it a bit more condensed as follows:
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) #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