Forum

Hallo,

ich mache so etwas immer mit einem 3 TRIGGERn
und einer Protokolltabelle.
Beispiel(delimiter ist #):

Originaltabelle:

CREATE TABLE DB.RBFDDT
(RBFDD_FELDNR SMALLINT NOT NULL
,RBFDD_MMKTOART CHAR(2 ) NOT NULL
,RBFDD_GILTVON DECIMAL(8 , 0 ) NOT NULL
,RBFDD_GILTBIS DECIMAL(8 , 0 ) NOT NULL
,RBFDD_MMKATEGORIE CHAR(1 ) NOT NULL
,RBFDD_BESCHREIBUNG CHAR(50 ) NOT NULL
,RBFDD_STELLENINT DECIMAL(2 , 0 ) NOT NULL
,RBFDD_STELLENEXT DECIMAL(2 , 0 )NOT NULL
,RBFDD_FORMATINT CHAR(1 ) NOT NULL
,RBFDD_KOMMAINT DECIMAL(1 , 0 ) NOT NULL
,RBFDD_MUMRECHENBAR CHAR(1 ) NOT NULL
,RBFDD_MSUMMIERBAR CHAR(1 ) NOT NULL
,RBFDD_LASTUSER CHAR(8 ) NOT NULL
WITH DEFAULT
,RBFDD_LASTUPDATE TIMESTAMP NOT NULL
WITH DEFAULT
,PRIMARY KEY
(RBFDD_FELDNR
,RBFDD_MMKTOART
,RBFDD_GILTVON
,RBFDD_GILTBIS
)
)
IN RU005D.RBFDDS
CCSID EBCDIC
#

Protokolltabelle:

CREATE TABLE DB.RBFDDTP
(RBFDDPSTATUS CHAR(8 ) NOT NULL
,RBFDDPTIMESTAMP TIMESTAMP NOT NULL
,RBFDDPFELDNR SMALLINT NOT NULL
,RBFDDPMMKTOART CHAR(2 ) NOT NULL
,RBFDDPGILTVON DECIMAL(8 , 0 ) NOT NULL
,RBFDDPGILTBIS DECIMAL(8 , 0 ) NOT NULL
,RBFDDPMMKATEGORIE CHAR(1 ) NOT NULL
,RBFDDPBESCHREIBUNG CHAR(50 ) NOT NULL
,RBFDDPSTELLENINT DECIMAL(2 , 0 ) NOT NULL
,RBFDDPSTELLENEXT DECIMAL(2 , 0 )NOT NULL
,RBFDDPFORMATINT CHAR(1 ) NOT NULL
,RBFDDPKOMMAINT DECIMAL(1 , 0 ) NOT NULL
,RBFDDPMUMRECHENBAR CHAR(1 ) NOT NULL
,RBFDDPMSUMMIERBAR CHAR(1 ) NOT NULL
,RBFDDPLASTUSER CHAR(8 ) NOT NULL
WITH DEFAULT
,RBFDDPLASTUPDATE TIMESTAMP NOT NULL
WITH DEFAULT
,PRIMARY KEY
(RBFDDPSTATUS
,RBFDDPTIMESTAMP
,RBFDDPFELDNR
,RBFDDPMMKTOART
,RBFDDPGILTVON
,RBFDDPGILTBIS
)
)
IN RU005D.RBFDDSP
CCSID EBCDIC
#

INSERT-Trigger

CREATE TRIGGER DB.RBFDDAI1
AFTER INSERT
ON DB.RBFDDT
REFERENCING NEW AS NEU
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO DB.RBFDDTP
( RBFDDPSTATUS , RBFDDPTIMESTAMP , RBFDDPFELDNR , RBFDDPMMKTOART ,
RBFDDPGILTVON , RBFDDPGILTBIS , RBFDDPMMKATEGORIE ,
RBFDDPBESCHREIBUNG , RBFDDPSTELLENINT , RBFDDPSTELLENEXT ,
RBFDDPFORMATINT , RBFDDPKOMMAINT , RBFDDPMUMRECHENBAR ,
RBFDDPMSUMMIERBAR , RBFDDPLASTUSER , RBFDDPLASTUPDATE )
VALUES ( ‚INSERT‘ , CURRENT TIMESTAMP , NEU.RBFDD_FELDNR ,
NEU.RBFDD_MMKTOART , NEU.RBFDD_GILTVON , NEU.RBFDD_GILTBIS ,
NEU.RBFDD_MMKATEGORIE , NEU.RBFDD_BESCHREIBUNG ,
NEU.RBFDD_STELLENINT , NEU.RBFDD_STELLENEXT , NEU.RBFDD_FORMATINT ,
NEU.RBFDD_KOMMAINT , NEU.RBFDD_MUMRECHENBAR ,
NEU.RBFDD_MSUMMIERBAR , NEU.RBFDD_LASTUSER , NEU.RBFDD_LASTUPDATE )
; END
#

UPDATE-Trigger
,, CREATE TRIGGER DB.RBFDDAU1
AFTER UPDATE
ON DB.RBFDDT
REFERENCING NEW AS NEU
OLD AS ALT
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO DB.RBFDDTP
( RBFDDPSTATUS , RBFDDPTIMESTAMP , RBFDDPFELDNR , RBFDDPMMKTOART ,
RBFDDPGILTVON , RBFDDPGILTBIS , RBFDDPMMKATEGORIE ,
RBFDDPBESCHREIBUNG , RBFDDPSTELLENINT , RBFDDPSTELLENEXT ,
RBFDDPFORMATINT , RBFDDPKOMMAINT , RBFDDPMUMRECHENBAR ,
RBFDDPMSUMMIERBAR , RBFDDPLASTUSER , RBFDDPLASTUPDATE )
VALUES ( ‚UPDATEA‘ , CURRENT TIMESTAMP , ALT.RBFDD_FELDNR ,
ALT.RBFDD_MMKTOART , ALT.RBFDD_GILTVON , ALT.RBFDD_GILTBIS ,
ALT.RBFDD_MMKATEGORIE , ALT.RBFDD_BESCHREIBUNG ,
ALT.RBFDD_STELLENINT , ALT.RBFDD_STELLENEXT , ALT.RBFDD_FORMATINT ,
ALT.RBFDD_KOMMAINT , ALT.RBFDD_MUMRECHENBAR ,
ALT.RBFDD_MSUMMIERBAR , ALT.RBFDD_LASTUSER , ALT.RBFDD_LASTUPDATE )
;
INSERT INTO DB.RBFDDTP
( RBFDDPSTATUS , RBFDDPTIMESTAMP , RBFDDPFELDNR , RBFDDPMMKTOART ,
RBFDDPGILTVON , RBFDDPGILTBIS , RBFDDPMMKATEGORIE ,
RBFDDPBESCHREIBUNG , RBFDDPSTELLENINT , RBFDDPSTELLENEXT ,
RBFDDPFORMATINT , RBFDDPKOMMAINT , RBFDDPMUMRECHENBAR ,
RBFDDPMSUMMIERBAR , RBFDDPLASTUSER , RBFDDPLASTUPDATE )
VALUES ( ‚UPDATEN‘ , CURRENT TIMESTAMP , NEU.RBFDD_FELDNR ,
NEU.RBFDD_MMKTOART , NEU.RBFDD_GILTVON , NEU.RBFDD_GILTBIS ,
NEU.RBFDD_MMKATEGORIE , NEU.RBFDD_BESCHREIBUNG ,
NEU.RBFDD_STELLENINT , NEU.RBFDD_STELLENEXT , NEU.RBFDD_FORMATINT ,
NEU.RBFDD_KOMMAINT , NEU.RBFDD_MUMRECHENBAR ,
NEU.RBFDD_MSUMMIERBAR , NEU.RBFDD_LASTUSER , NEU.RBFDD_LASTUPDATE )
; END
#
siehe Teil 2