zOs/TX/WK976DDL

------------$-{right(userid() sysvar(sysnode) date(s) time(), 60, '-')}
-- testCase $dsn
-- env      $env      phase $phase
-- subsys   $subsys     db $db       creator $creator
-----------------------------------------------------------------------
---||| begin ddl testcase $mbr env $env phase $phase
SET CURRENT SQLID='S100447';
SET CURRENT SCHEMA = $creator;
drop tablespace $db.a976A;
drop tablespace $db.a976B;
  commit;
$@[
if $phase >= 1 then $@=[
  CREATE TABLESPACE A976A
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    FREEPAGE 10 PCTFREE 10
    GBPCACHE CHANGED
    TRACKMOD YES
    SEGSIze 64
    BUFFERPOOL BP2
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID      EBCDIC
    DEFINE YES
    MAXROWS 255;
  CREATE TABLE $creator.Twk976A2
     (Wk976CH              char(10) not null
     ,wk976chb             char(10) NOT NULL
     ,wk976inC             int      NOT NULL with default
     )
    IN $db.A976A
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID      EBCDIC
    NOT VOLATILE
;
insert into $creator.twk976a2 values('a', 'a', 0);
insert into $creator.twk976a2 values('b', 'b', 1);
--
  COMMIT;
--
$]
if $phase >= 2 then $@=[
------------------------------------------------------------------------
--    Table=$creator.Twk976A1 In $db.A976A
------------------------------------------------------------------------
--
  CREATE TABLE $creator.Twk976A1
     (Wk976CH              char(10) not null
     ,wk976chb             char(10) NOT NULL
     ,wk976inC             int      NOT NULL with default
     )
    IN $db.A976A
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID      EBCDIC
    NOT VOLATILE
;
SET CURRENT PATH = "SYSIBM","SYSPROC","$creator"
;
create trigger $creator.twk976Upd  no cascade before
               update of WK976CHB
    on $creator.twk976A1        referencing Old as o New as n
    for each row mode db2sql
         set n.WK976inc = o.wk976inc + $phase
;
insert into $creator.twk976a1 values('a', 'a', 0);
$= upd = update $creator.twk976a1 $*+
    set WK976CHB = strip(WK976CHB) || '$phase'
$upd ;
insert into $creator.twk976a1 values('b', 'b', 0);
$upd ;
insert into $creator.twk976a1 values('c', 'c', 0);
$upd ;
  CREATE VIEW $creator.VWK976A2 as
    SELECT * from  $creator.twk976A2
;
$*(
create trigger $creator.vwk976Upd  instead of update
    on $creator.vwk976A2        referencing Old as o New as n
    for each row mode db2sql
        insert into $creator.twk976B2
          values (n.wk976ch, 'triPha$phase', o.wk976inC)
;
$*)
create trigger $creator.vwk976Upd  instead of update
    on $creator.vwk976A2        referencing Old as o New as n
    for each row mode db2sql
        SIGNAL SQLSTATE '77ABC' set message_text =
          'trigger vwk976Upd phase $phase <' || n.wk976ch || '>'
;
commit;
-- update $creator.vwk976A2 set wk976ch = 'view';
$]
$]