Hi,
I have written a trigger like below.

CREATE TRIGGER RQ5.NON_SWIFT_TXN_CLNT_UPT AFTER UPDATE OF BUS_OR_ENTY_NM ON RQ5.NON_SWIFT_TXN_CLNT REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
INSERT INTO RQ5.NON_SWIFT_TXN_INFO_HIST
SELECT NON_SWIFT_ID, CURRENT TIMESTAMP, USER, 'P', EFTR_INCDNT_ID, SRC_SYS, PROC_DT, MSG_FLOW_TYP, REC_ID_NO, SENT_TM, TRNSMT_DT, TXN_AMT, CRNCY_CD, EXCH_RT,DR_CR_IND
FROM RQ5.NON_SWIFT_TXN_INFO WHERE NON_SWIFT_ID = OLD.NON_SWIFT_ID
;--
INSERT INTO RQ5.NON_SWIFT_TXN_CLNT_HIST
SELECT NON_SWIFT_ID, CLNT_TYP, CURRENT TIMESTAMP, DOB, INDV_DOC_ID, INDV_OTH_NM, INDV_GIVEN_NM, INDV_SURNM, TEL_NO, OTH_ID_DESC, CLNT_NO, INDV_OCCTN, BUS_OR_ENTY_NM, CNTRY_CD, SUBCNTRY_CD, PSTL_ZIP_CD, CTY_NM, ST_ADDR, OTH_SUBCNTRY_NM, US_PSTCD_EXT, ST_ADDR_2, ST_ADDR_3, ST_ADDR_4, ACCT_NO, LOC_CD
FROM RQ5.NON_SWIFT_TXN_CLNT
WHERE NON_SWIFT_ID = OLD.NON_SWIFT_ID
;--
END;

But it inserts the record after updating the column. But my requirement is records need to be inserted in HIST table before updating the column in main table. I mean to say I need old values in HIST tables. In first insert there will be only one record for one NON_SWIFT_ID in main table, but in second insert there will be multiple records for one NON_SWIFT_ID in main table. All records belonging to same NON_SWIFT_ID should go to HIST table. Please let me know how to do it.

Thanks
Surjya

if you wanted a BEFORE trigger, you should have declared it as BEFORE, not AFTER.

If I will use before update, then I can not intiate any operation on other table. Hence I can not use before update. I am looking for is there any other way.

Except doing this in the application, you can only use AFTER triggers to modify other tables. If you think about cascaded triggers, this makes a lot of sense, actually.

Other question: An INSERT/UPDATE statement is an atomic operation. So why do you have to do the INSERT before the UPDATE? There is absolutely no need for it. You do have access to the old row in the trigger and can insert those old values into the history table.

Thanks stolze,
I may be wrong, if I will use old as old then I will be able to insert only one record into hist table, not all records having same non_swift_id.
Let say if will create trigger like below
CREATE TRIGGER RQ5.NON_SWIFT_TXN_CLNT_UPT AFTER UPDATE OF BUS_OR_ENTY_NM ON RQ5.NON_SWIFT_TXN_CLNT REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
INSERT INTO RQ5.NON_SWIFT_TXN_CLNT_HIST
values( OLD.NON_SWIFT_ID, OLD.CLNT_TYP, CURRENT TIMESTAMP, OLD.DOB, OLD.INDV_DOC_ID, OLD.INDV_OTH_NM, OLD.INDV_GIVEN_NM, OLD.INDV_SURNM, OLD.TEL_NO)
;--
END
If there are four records having same non_swift_id, will it insert all four records into history table even though I update only one record in main table.
Please provide some direction on it.

Thanks
Surjya

This is a FOR EACH ROW trigger. That means, the trigger fires for each row that is touched by the UPDATE operation. If there are rows with duplicated values and you update only one of those rows, the trigger fires only once. And the way you have written the trigger body implies that only a single row will be inserted into RQ5.NON_SWIFT_TXN_CLNT_HIST. If you want to copy all 4 rows, you'll need an INSERT INTO ... SELECT ... FROM ... WHERE ... to find all rows to be copied and insert them into the history table.