zOs/SQL/MIRCOA
--#SET TERMINATOR ?
SET CURRENT SQLID='S100447'?
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","OA1P" ?
CREATE TRIGGER OA1A.VFI027A1_UPD
INSTEAD OF UPDATE ON OA1A.VFI027A1
REFERENCING NEW AS N
OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
VALUES(CASE WHEN(O.TKKEY < 1700001 AND N.TKKEY < 1700001) OR (O.
TKKEY BETWEEN 1700001 AND 3200000 AND N.TKKEY BETWEEN
1700001 AND 3200000) OR (O.TKKEY BETWEEN 3200001 AND
4500000 AND N.TKKEY BETWEEN 3200001 AND 4500000) OR (O.
TKKEY BETWEEN 4500001 AND 5000000 AND N.TKKEY BETWEEN
4500001 AND 5000000) OR (O.TKKEY BETWEEN 5000001 AND
6980000 AND N.TKKEY BETWEEN 5000001 AND 6980000) OR (O.
TKKEY BETWEEN 6980001 AND 9200000 AND N.TKKEY BETWEEN
6980001 AND 9200000) OR (O.TKKEY BETWEEN 9200001 AND
10000000 AND N.TKKEY BETWEEN 9200001 AND 10000000) OR (
O.TKKEY BETWEEN 10000001 AND 10500000 AND N.TKKEY
BETWEEN 10000001 AND 10500000) OR (O.TKKEY BETWEEN
10500001 AND 11000000 AND N.TKKEY BETWEEN 10500001 AND
11000000) OR (O.TKKEY > 11000000 AND N.TKKEY > 11000000
) THEN 0 ELSE RAISE_ERROR('70001',
'TKKEY update in a different phys. table is not possible vi
a trigger, use a separate insert and delete statement'
) END) ;
VALUES(CASE WHEN N.BOERSE = O.BOERSE AND N.WHRG = O.WHRG AND N.
KURSDATUM = O.KURSDATUM THEN 0 ELSE RAISE_ERROR('70002',
'Some key values (Boerse/Whrg/Kursdatum) cannot be changed,
message generated because of trigger condition'
) END) ;
UPDATE OA1A.TFI027A1 U
SET (U.TKKEY, U.VERFALL, U.STATUS, U.MUT_TIMESTAMP,
U.TK_KURSTYP, U.TK_ZUSATZ, U.CS_KA_NUM, U.KURS_LIEFERUNG,
U.KURS_UMRECHNUNG, U.MENGE, U.BOERSE_TK, U.ERSTELLUNG,
U.QUELLE, U.LOG_EINTRAG, U.INSTRAD, U.KS_BPL_ST,
U.KS_BPL_HT, U.KS_BPL_KS, U.KS_ALT_KS, U.KS_BPL_HA,
U.KS_AIS_SP, U.KS_BPL_IN, U.KS_BPL_SP, U.EXCODE) = (N.TKKEY,
N.VERFALL, N.STATUS, CURRENT TIMESTAMP, N.TK_KURSTYP,
N.TK_ZUSATZ, N.CS_KA_NUM, N.KURS_LIEFERUNG,
N.KURS_UMRECHNUNG, N.MENGE, N.BOERSE_TK, N.ERSTELLUNG,
N.QUELLE, N.LOG_EINTRAG, N.INSTRAD, N.KS_BPL_ST,
N.KS_BPL_HT, N.KS_BPL_KS, N.KS_ALT_KS, N.KS_BPL_HA,
N.KS_AIS_SP, N.KS_BPL_IN, N.KS_BPL_SP, N.EXCODE)
WHERE O.TKKEY < 1700001
AND N.TKKEY < 1700001
AND O.TKKEY = U.TKKEY
AND O.BOERSE = U.BOERSE
AND O.WHRG = U.WHRG
AND O.KURSDATUM = U.KURSDATUM
AND O.VERFALL = U.VERFALL
AND O.STATUS = U.STATUS
AND O.MUT_TIMESTAMP = U.MUT_TIMESTAMP ;
UPDATE OA1A.TFI027B1 U
SET (U.TKKEY, U.VERFALL, U.STATUS, U.MUT_TIMESTAMP,
U.TK_KURSTYP, U.TK_ZUSATZ, U.CS_KA_NUM, U.KURS_LIEFERUNG,
U.KURS_UMRECHNUNG, U.MENGE, U.BOERSE_TK, U.ERSTELLUNG,
U.QUELLE, U.LOG_EINTRAG, U.INSTRAD, U.KS_BPL_ST,
U.KS_BPL_HT, U.KS_BPL_KS, U.KS_ALT_KS, U.KS_BPL_HA,
U.KS_AIS_SP, U.KS_BPL_IN, U.KS_BPL_SP, U.EXCODE) = (N.TKKEY,
N.VERFALL, N.STATUS, CURRENT TIMESTAMP, N.TK_KURSTYP,
N.TK_ZUSATZ, N.CS_KA_NUM, N.KURS_LIEFERUNG,
N.KURS_UMRECHNUNG, N.MENGE, N.BOERSE_TK, N.ERSTELLUNG,
N.QUELLE, N.LOG_EINTRAG, N.INSTRAD, N.KS_BPL_ST,
N.KS_BPL_HT, N.KS_BPL_KS, N.KS_ALT_KS, N.KS_BPL_HA,
N.KS_AIS_SP, N.KS_BPL_IN, N.KS_BPL_SP, N.EXCODE)
WHERE O.TKKEY BETWEEN 1700001 AND 3200000
AND N.TKKEY BETWEEN 1700001 AND 3200000
AND O.TKKEY = U.TKKEY
AND O.BOERSE = U.BOERSE
AND O.WHRG = U.WHRG
AND O.KURSDATUM = U.KURSDATUM
AND O.VERFALL = U.VERFALL
AND O.STATUS = U.STATUS
AND O.MUT_TIMESTAMP = U.MUT_TIMESTAMP ;
UPDATE OA1A.TFI027C1 U
SET (U.TKKEY, U.VERFALL, U.STATUS, U.MUT_TIMESTAMP,
U.TK_KURSTYP, U.TK_ZUSATZ, U.CS_KA_NUM, U.KURS_LIEFERUNG,
U.KURS_UMRECHNUNG, U.MENGE, U.BOERSE_TK, U.ERSTELLUNG,
U.QUELLE, U.LOG_EINTRAG, U.INSTRAD, U.KS_BPL_ST,
U.KS_BPL_HT, U.KS_BPL_KS, U.KS_ALT_KS, U.KS_BPL_HA,
U.KS_AIS_SP, U.KS_BPL_IN, U.KS_BPL_SP, U.EXCODE) = (N.TKKEY,
N.VERFALL, N.STATUS, CURRENT TIMESTAMP, N.TK_KURSTYP,
N.TK_ZUSATZ, N.CS_KA_NUM, N.KURS_LIEFERUNG,
N.KURS_UMRECHNUNG, N.MENGE, N.BOERSE_TK, N.ERSTELLUNG,
N.QUELLE, N.LOG_EINTRAG, N.INSTRAD, N.KS_BPL_ST,
N.KS_BPL_HT, N.KS_BPL_KS, N.KS_ALT_KS, N.KS_BPL_HA,
N.KS_AIS_SP, N.KS_BPL_IN, N.KS_BPL_SP, N.EXCODE)
WHERE O.TKKEY BETWEEN 3200001 AND 4500000
AND N.TKKEY BETWEEN 3200001 AND 4500000
AND O.TKKEY = U.TKKEY
AND O.BOERSE = U.BOERSE
AND O.WHRG = U.WHRG
AND O.KURSDATUM = U.KURSDATUM
AND O.VERFALL = U.VERFALL
AND O.STATUS = U.STATUS
AND O.MUT_TIMESTAMP = U.MUT_TIMESTAMP ;
UPDATE OA1A.TFI027D1 U
SET (U.TKKEY, U.VERFALL, U.STATUS, U.MUT_TIMESTAMP,
U.TK_KURSTYP, U.TK_ZUSATZ, U.CS_KA_NUM, U.KURS_LIEFERUNG,
U.KURS_UMRECHNUNG, U.MENGE, U.BOERSE_TK, U.ERSTELLUNG,
U.QUELLE, U.LOG_EINTRAG, U.INSTRAD, U.KS_BPL_ST,
U.KS_BPL_HT, U.KS_BPL_KS, U.KS_ALT_KS, U.KS_BPL_HA,
U.KS_AIS_SP, U.KS_BPL_IN, U.KS_BPL_SP, U.EXCODE) = (N.TKKEY,
N.VERFALL, N.STATUS, CURRENT TIMESTAMP, N.TK_KURSTYP,
N.TK_ZUSATZ, N.CS_KA_NUM, N.KURS_LIEFERUNG,
N.KURS_UMRECHNUNG, N.MENGE, N.BOERSE_TK, N.ERSTELLUNG,
N.QUELLE, N.LOG_EINTRAG, N.INSTRAD, N.KS_BPL_ST,
N.KS_BPL_HT, N.KS_BPL_KS, N.KS_ALT_KS, N.KS_BPL_HA,
N.KS_AIS_SP, N.KS_BPL_IN, N.KS_BPL_SP, N.EXCODE)
WHERE O.TKKEY BETWEEN 4500001 AND 5000000
AND N.TKKEY BETWEEN 4500001 AND 5000000
AND O.TKKEY = U.TKKEY
AND O.BOERSE = U.BOERSE
AND O.WHRG = U.WHRG
AND O.KURSDATUM = U.KURSDATUM
AND O.VERFALL = U.VERFALL
AND O.STATUS = U.STATUS
AND O.MUT_TIMESTAMP = U.MUT_TIMESTAMP ;
UPDATE OA1A.TFI027E1 U
SET (U.TKKEY, U.VERFALL, U.STATUS, U.MUT_TIMESTAMP,
U.TK_KURSTYP, U.TK_ZUSATZ, U.CS_KA_NUM, U.KURS_LIEFERUNG,
U.KURS_UMRECHNUNG, U.MENGE, U.BOERSE_TK, U.ERSTELLUNG,
U.QUELLE, U.LOG_EINTRAG, U.INSTRAD, U.KS_BPL_ST,
U.KS_BPL_HT, U.KS_BPL_KS, U.KS_ALT_KS, U.KS_BPL_HA,
U.KS_AIS_SP, U.KS_BPL_IN, U.KS_BPL_SP, U.EXCODE) = (N.TKKEY,
N.VERFALL, N.STATUS, CURRENT TIMESTAMP, N.TK_KURSTYP,
N.TK_ZUSATZ, N.CS_KA_NUM, N.KURS_LIEFERUNG,
N.KURS_UMRECHNUNG, N.MENGE, N.BOERSE_TK, N.ERSTELLUNG,
N.QUELLE, N.LOG_EINTRAG, N.INSTRAD, N.KS_BPL_ST,
N.KS_BPL_HT, N.KS_BPL_KS, N.KS_ALT_KS, N.KS_BPL_HA,
N.KS_AIS_SP, N.KS_BPL_IN, N.KS_BPL_SP, N.EXCODE)
WHERE O.TKKEY BETWEEN 5000001 AND 6980000
AND N.TKKEY BETWEEN 5000001 AND 6980000
AND O.TKKEY = U.TKKEY
AND O.BOERSE = U.BOERSE
AND O.WHRG = U.WHRG
AND O.KURSDATUM = U.KURSDATUM
AND O.VERFALL = U.VERFALL
AND O.STATUS = U.STATUS
AND O.MUT_TIMESTAMP = U.MUT_TIMESTAMP ;
UPDATE OA1A.TFI027F1 U
SET (U.TKKEY, U.VERFALL, U.STATUS, U.MUT_TIMESTAMP,
U.TK_KURSTYP, U.TK_ZUSATZ, U.CS_KA_NUM, U.KURS_LIEFERUNG,
U.KURS_UMRECHNUNG, U.MENGE, U.BOERSE_TK, U.ERSTELLUNG,
U.QUELLE, U.LOG_EINTRAG, U.INSTRAD, U.KS_BPL_ST,
U.KS_BPL_HT, U.KS_BPL_KS, U.KS_ALT_KS, U.KS_BPL_HA,
U.KS_AIS_SP, U.KS_BPL_IN, U.KS_BPL_SP, U.EXCODE) = (N.TKKEY,
N.VERFALL, N.STATUS, CURRENT TIMESTAMP, N.TK_KURSTYP,
N.TK_ZUSATZ, N.CS_KA_NUM, N.KURS_LIEFERUNG,
N.KURS_UMRECHNUNG, N.MENGE, N.BOERSE_TK, N.ERSTELLUNG,
N.QUELLE, N.LOG_EINTRAG, N.INSTRAD, N.KS_BPL_ST,
N.KS_BPL_HT, N.KS_BPL_KS, N.KS_ALT_KS, N.KS_BPL_HA,
N.KS_AIS_SP, N.KS_BPL_IN, N.KS_BPL_SP, N.EXCODE)
WHERE O.TKKEY BETWEEN 6980001 AND 9200000
AND N.TKKEY BETWEEN 6980001 AND 9200000
AND O.TKKEY = U.TKKEY
AND O.BOERSE = U.BOERSE
AND O.WHRG = U.WHRG
AND O.KURSDATUM = U.KURSDATUM
AND O.VERFALL = U.VERFALL
AND O.STATUS = U.STATUS
AND O.MUT_TIMESTAMP = U.MUT_TIMESTAMP ;
UPDATE OA1A.TFI027G1 U
SET (U.TKKEY, U.VERFALL, U.STATUS, U.MUT_TIMESTAMP,
U.TK_KURSTYP, U.TK_ZUSATZ, U.CS_KA_NUM, U.KURS_LIEFERUNG,
U.KURS_UMRECHNUNG, U.MENGE, U.BOERSE_TK, U.ERSTELLUNG,
U.QUELLE, U.LOG_EINTRAG, U.INSTRAD, U.KS_BPL_ST,
U.KS_BPL_HT, U.KS_BPL_KS, U.KS_ALT_KS, U.KS_BPL_HA,
U.KS_AIS_SP, U.KS_BPL_IN, U.KS_BPL_SP, U.EXCODE) = (N.TKKEY,
N.VERFALL, N.STATUS, CURRENT TIMESTAMP, N.TK_KURSTYP,
N.TK_ZUSATZ, N.CS_KA_NUM, N.KURS_LIEFERUNG,
N.KURS_UMRECHNUNG, N.MENGE, N.BOERSE_TK, N.ERSTELLUNG,
N.QUELLE, N.LOG_EINTRAG, N.INSTRAD, N.KS_BPL_ST,
N.KS_BPL_HT, N.KS_BPL_KS, N.KS_ALT_KS, N.KS_BPL_HA,
N.KS_AIS_SP, N.KS_BPL_IN, N.KS_BPL_SP, N.EXCODE)
WHERE O.TKKEY BETWEEN 9200001 AND 10000000
AND N.TKKEY BETWEEN 9200001 AND 10000000
AND O.TKKEY = U.TKKEY
AND O.BOERSE = U.BOERSE
AND O.WHRG = U.WHRG
AND O.KURSDATUM = U.KURSDATUM
AND O.VERFALL = U.VERFALL
AND O.STATUS = U.STATUS
AND O.MUT_TIMESTAMP = U.MUT_TIMESTAMP ;
UPDATE OA1A.TFI027H1 U
SET (U.TKKEY, U.VERFALL, U.STATUS, U.MUT_TIMESTAMP,
U.TK_KURSTYP, U.TK_ZUSATZ, U.CS_KA_NUM, U.KURS_LIEFERUNG,
U.KURS_UMRECHNUNG, U.MENGE, U.BOERSE_TK, U.ERSTELLUNG,
U.QUELLE, U.LOG_EINTRAG, U.INSTRAD, U.KS_BPL_ST,
U.KS_BPL_HT, U.KS_BPL_KS, U.KS_ALT_KS, U.KS_BPL_HA,
U.KS_AIS_SP, U.KS_BPL_IN, U.KS_BPL_SP, U.EXCODE) = (N.TKKEY,
N.VERFALL, N.STATUS, CURRENT TIMESTAMP, N.TK_KURSTYP,
N.TK_ZUSATZ, N.CS_KA_NUM, N.KURS_LIEFERUNG,
N.KURS_UMRECHNUNG, N.MENGE, N.BOERSE_TK, N.ERSTELLUNG,
N.QUELLE, N.LOG_EINTRAG, N.INSTRAD, N.KS_BPL_ST,
N.KS_BPL_HT, N.KS_BPL_KS, N.KS_ALT_KS, N.KS_BPL_HA,
N.KS_AIS_SP, N.KS_BPL_IN, N.KS_BPL_SP, N.EXCODE)
WHERE O.TKKEY BETWEEN 10000001 AND 10500000
AND N.TKKEY BETWEEN 10000001 AND 10500000
AND O.TKKEY = U.TKKEY
AND O.BOERSE = U.BOERSE
AND O.WHRG = U.WHRG
AND O.KURSDATUM = U.KURSDATUM
AND O.VERFALL = U.VERFALL
AND O.STATUS = U.STATUS
AND O.MUT_TIMESTAMP = U.MUT_TIMESTAMP ;
UPDATE OA1A.TFI027I1 U
SET (U.TKKEY, U.VERFALL, U.STATUS, U.MUT_TIMESTAMP,
U.TK_KURSTYP, U.TK_ZUSATZ, U.CS_KA_NUM, U.KURS_LIEFERUNG,
U.KURS_UMRECHNUNG, U.MENGE, U.BOERSE_TK, U.ERSTELLUNG,
U.QUELLE, U.LOG_EINTRAG, U.INSTRAD, U.KS_BPL_ST,
U.KS_BPL_HT, U.KS_BPL_KS, U.KS_ALT_KS, U.KS_BPL_HA,
U.KS_AIS_SP, U.KS_BPL_IN, U.KS_BPL_SP, U.EXCODE) = (N.TKKEY,
N.VERFALL, N.STATUS, CURRENT TIMESTAMP, N.TK_KURSTYP,
N.TK_ZUSATZ, N.CS_KA_NUM, N.KURS_LIEFERUNG,
N.KURS_UMRECHNUNG, N.MENGE, N.BOERSE_TK, N.ERSTELLUNG,
N.QUELLE, N.LOG_EINTRAG, N.INSTRAD, N.KS_BPL_ST,
N.KS_BPL_HT, N.KS_BPL_KS, N.KS_ALT_KS, N.KS_BPL_HA,
N.KS_AIS_SP, N.KS_BPL_IN, N.KS_BPL_SP, N.EXCODE)
WHERE O.TKKEY BETWEEN 10500001 AND 11000000
AND N.TKKEY BETWEEN 10500001 AND 11000000
AND O.TKKEY = U.TKKEY
AND O.BOERSE = U.BOERSE
AND O.WHRG = U.WHRG
AND O.KURSDATUM = U.KURSDATUM
AND O.VERFALL = U.VERFALL
AND O.STATUS = U.STATUS
AND O.MUT_TIMESTAMP = U.MUT_TIMESTAMP ;
UPDATE OA1A.TFI027J1 U
SET (U.TKKEY, U.VERFALL, U.STATUS, U.MUT_TIMESTAMP,
U.TK_KURSTYP, U.TK_ZUSATZ, U.CS_KA_NUM, U.KURS_LIEFERUNG,
U.KURS_UMRECHNUNG, U.MENGE, U.BOERSE_TK, U.ERSTELLUNG,
U.QUELLE, U.LOG_EINTRAG, U.INSTRAD, U.KS_BPL_ST,
U.KS_BPL_HT, U.KS_BPL_KS, U.KS_ALT_KS, U.KS_BPL_HA,
U.KS_AIS_SP, U.KS_BPL_IN, U.KS_BPL_SP, U.EXCODE) = (N.TKKEY,
N.VERFALL, N.STATUS, CURRENT TIMESTAMP, N.TK_KURSTYP,
N.TK_ZUSATZ, N.CS_KA_NUM, N.KURS_LIEFERUNG,
N.KURS_UMRECHNUNG, N.MENGE, N.BOERSE_TK, N.ERSTELLUNG,
N.QUELLE, N.LOG_EINTRAG, N.INSTRAD, N.KS_BPL_ST,
N.KS_BPL_HT, N.KS_BPL_KS, N.KS_ALT_KS, N.KS_BPL_HA,
N.KS_AIS_SP, N.KS_BPL_IN, N.KS_BPL_SP, N.EXCODE)
WHERE O.TKKEY > 11000000
AND N.TKKEY > 11000000
AND O.TKKEY = U.TKKEY
AND O.BOERSE = U.BOERSE
AND O.WHRG = U.WHRG
AND O.KURSDATUM = U.KURSDATUM
AND O.VERFALL = U.VERFALL
AND O.STATUS = U.STATUS
AND O.MUT_TIMESTAMP = U.MUT_TIMESTAMP ;
END ?
--#SET TERMINATOR ;
COMMIT;