zOs/SQL/CRETWKUP

select * from A540769.TWK001A_UPD;
x
select current timestamp from sysibm.sysDummy1;
--#SET TERMINATOR ?
CREATE TRIGGER A540769.TWK001_upd_TRIG
  AFTER UPDATE ON A540769.TWK001A
    REFERENCING NEW       AS NEW
                OLD       AS OLD
    FOR EACH ROW  MODE DB2SQL
    begin atomic
    INSERT INTO A540769.TWK001A_UPD
      (FUN, KEY)
       values( 'U', new.wk001name);
    INSERT INTO A540769.TWK001A_UPD
      (FUN, KEY)
      (select 'O', old.wk001name from sysibm.sysDummy1
                   where old.wk001Name <> new.wk001Name
      )
      ;
     END
?
--#SET TERMINATOR ;
select current timestamp from sysibm.sysDummy1;
commit;
select current timestamp from sysibm.sysDummy1;
select * from A540769.TWK001A_UPD;
x
;
select current timestamp from sysibm.sysDummy1;
commit;
select current timestamp from sysibm.sysDummy1;
x
  select * from A540769.TWK001A;
  select * from A540769.TWK001A_UPD;
  INSERT INTO A540769.TWK001A  (WK001NAME, WK001NUM, wk001t1)
      VALUES ('drei', 3, 'a');
  INSERT INTO A540769.TWK001A  (WK001NAME, WK001NUM, wk001t1)
      VALUES ('vier', 4, 'a');
  select * from A540769.TWK001A;
  update      A540769.TWK001A set wk001Name = left(wk001Name,9) || 'x' ;
  delete from A540769.TWK001A where wk001Num in (3, 4);
  select * from A540769.TWK001A;
  select * from A540769.TWK001A_UPD;
cOMMIT;
  x
  INSERT INTO A540769.TWK001A_UPD  (FUN, KEY)
      VALUES ('t', 'keyEins');
  INSERT into A540769.TWK001A_UPD  (FUN, KEY)
      VALUES ('t', 'keyZwei');
  INSERT into A540769.TWK001A_UPD  (FUN, KEY)
    ( select fun, 'x' || left(key, 10)
         from A540769.TWK001A_UPD
    )
  ;
  select * from A540769.TWK001A_UPD
  ;
cOMMIT;
  x
  INSERT INTO A540769.TWK001A  (WK001NAME, WK001NUM, wk001t1)
      VALUES ('eins', 1, 'a');
  INSERT INTO A540769.TWK001A  (WK001NAME, WK001NUM, wk001t1)
      VALUES ('zwei', 2, 'a');
  SET CURRENT SQLID = 'S100447';
--
  CREATE TABLESPACE U001U
    IN DA540769
    USING STOGROUP GSMS
    PRIQTY 12 SECQTY 7200
    ERASE  NO
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    TRACKMOD YES
    SEGSIZE 64
    BUFFERPOOL BP2
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID      EBCDIC
    DEFINE YES
    MAXROWS 255;
--
  COMMIT;
--
------------------------------------------------------------------------
--    Table=A540769.TWK001A In DA540769.A001A
------------------------------------------------------------------------
--
  CREATE TABLE A540769.TWK001A_UPD
     (TST                  TIMESTAMP NOT NULL WITH DEFAULT,
      FUN                  CHAR(1) NOT NULL,
      KEY                  CHAR(20)
     )
    IN DA540769.U001U
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID      EBCDIC
    NOT VOLATILE;
--
  drop          INDEX A540769.IWK001A_UPD ;
  commit;
  CREATE        iNDEX A540769.IWK001A_UPD
    ON A540769.TWK001A_UPD
     (TST                    ASC)
    USING STOGROUP GSMS
    PRIQTY 12 SECQTY 12
    ERASE  NO
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    NOT CLUSTER
    BUFFERPOOL BP1
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 2 G;
--
  COMMIT;
-- drop   TRIGGER A540769.TWK001_DEL_TRIG;
CREATE TRIGGER A540769.TWK001_DEL_TRIG
  AFTER DELETE ON A540769.TWK001A
    REFERENCING OLD_Table AS OLD
    FOR EACH ROW       MODE DB2SQL
    INSERT INTO A540769.TWK001A_UPD
      (FUN, KEY)
    ( select 'D', old.wk001name from old)
;
cOMMIT;
DROP   TRIGGER A540769.TWK001_INS_TRIG ;
CREATE TRIGGER A540769.TWK001_INS_TRIG
  AFTER INSERT ON A540769.TWK001A
    REFERENCING NEW_Table AS NEW
    FOR EACH statement  MODE DB2SQL
    INSERT INTO A540769.TWK001A_UPD
      (FUN, KEY)
    ( select 'I', new.wk001name from new)
--#SET TERMINATOR ?
CREATE TRIGGER A540769.TWK001_upd_TRIG
  AFTER UPDATE ON A540769.TWK001A
    REFERENCING NEW       AS NEW
                OLD       AS OLD
    FOR EACH ROW  MODE DB2SQL
    begin atomic
    INSERT INTO A540769.TWK001A_UPD
      (FUN, KEY)
       values( 'U', new.wk001name);
    INSERT INTO A540769.TWK001A_UPD
      (FUN, KEY)
      (select 'O', old.wk001name from sysibm.sysDummy1
                   where old.wk001Name <> new.wk001Name
      )
      ;
     END
?
--#SET TERMINATOR ;
commit;