By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

IF statement as SELECT's argument for a DB2 Trigger

P: 7
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
Feb 16 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.