Hi,
I have a problem for a DB2 trigger that take records from a table and put them into another one.
I need to do a choice when i copy from a table to another. I made my trigger and it works but it's not perfect. So i need help to make it better.
Here is my trigger:
CREATE TRIGGER SAPR3.TR1_P_SGQ0400I AFTER INSERT ON SAPR3.SGQ0400I
REFERENCING NEW ROW AS NEW FOR EACH ROW MODE DB2SQL
WHEN (NEW.STATO = 'FOR_EXPORT')
BEGIN ATOMIC
INSERT INTO SAPR3.P_SGQ0400I(
KRICH,
BUKRS,
CANPR,
DTEVE,
DTFPI,
TIOPE,
CDAVV,
TIPLA,
STOPE,
KCONCE,
CARSE,
CODRIC,
DTDECR,
NOMRIC,
KFISCR,
KPIVAR,
FLTRD,
CODCLE,
DTDECC,
NOMCL,
KFISC,
KPIVA,
TIPME,
PRESA,
DTDECF,
CLMIS,
FORID,
FOCAP,
NCIV1,
NCIV2,
COMUN,
PROVI,
RID,
STATO,
ADB_SUBJECT,
ADB_SEQUENCE,
ADB_TIMESTAMP,
ADB_OPCODE,
/* ADB_SET_SEQUENCE,
ADB_UPDATE_ALL,
ADB_L_CMSEQUENCE, */
ADB_L_DELIVERY)
select
SELECT TRIM(NEW.KRICH), TRIM(NEW.BUKRS), TRIM(NEW.CANPR), sleexists , NEW.DTFPI, TRIM(NEW.TIOPE), TRIM(NEW.CDAVV), TRIM(NEW.TIPLA), TRIM(NEW.STOPE), TRIM(NEW.KCONCE), TRIM(NEW.CARSE), TRIM(NEW.CODRIC), NEW.DTDECR, TRIM(NEW.NOMRIC), TRIM(NEW.KFISCR), TRIM(NEW.KPIVAR), TRIM(NEW.FLTRD), TRIM(NEW.CODCLE), NEW.DTDECC, TRIM(NEW.NOMCL), TRIM(NEW.KFISC), TRIM(NEW.KPIVA), TRIM(NEW.TIPME), TRIM(NEW.PRESA), NEW.DTDECF, TRIM(NEW.CLMIS), TRIM(NEW.FORID), TRIM(NEW.FOCAP), TRIM(NEW.NCIV1), TRIM(NEW.NCIV2), TRIM(NEW.COMUN), TRIM(NEW.PROVI), TRIM(NEW.RID), TRIM(NEW.STATO), '', ADB_SEQ, CURRENT TIMESTAMP, 1, 'N'
FROM SAPR3.ADB_SEQTAB
WHERE PUB_TABLE = 'P_SGQ0400I';
UPDATE SAPR3.P_SGQ0400I SET DTEVE= NULL
WHERE DTEVE= 0;
/* O UPDATE SAPR3.P_SGQ0400I SET DTEVE= NULL
WHERE NEW.DTEVE= 0; */
UPDATE SAPR3.P_SGQ0400I SET DTFPI= NULL
WHERE DTFPI= 0;
UPDATE SAPR3.P_SGQ0400I SET DTDECR= NULL
WHERE DTDECR= 0;
UPDATE SAPR3.P_SGQ0400I SET DTEVE= NULL
WHERE DTDECC= 0;
UPDATE SAPR3.P_SGQ0400I SET DTDECF= NULL
WHERE DTDECF= 0;
UPDATE SAPR3.ADB_SEQTAB SET ADB_SEQ = ADB_SEQ + 1
WHERE PUB_TABLE = 'P_SGQ0400I';
END;
It's good until the table where i put the records is not big. But when the number of records increases it may have some TIME problem. Could anyone help me to make it better about the update?
thank you